import time
from re import split

from HLog import HLog
import configparser
import os
import sys
import shutil
import csv
from dataAnlysis import headMain
#from package import const, twanalysis, thanalysis
from package.const import const
from package.MysqlHelp import _MySqlHelper
import datetime
import zipfile
import tarfile
import gol
import decimal

mysql = _MySqlHelper()
dictc = {}


def del_file(path):
    ls = os.listdir(path)
    for i in ls:
        c_path = os.path.join(path, i)
        if os.path.isdir(c_path):
            del_file(c_path)
            shutil.rmtree(c_path)
        else:
            os.remove(c_path)


def add_1(binary_inpute):  # 二进制编码加1
    _, out = bin(int(binary_inpute, 2) + 1).split("b")
    return out


def reverse(binary_inpute):  # 取反操作
    binary_inpute = list(binary_inpute)
    binary_out = binary_inpute
    for epoch, i in enumerate(binary_out):
        if i == "0":
            binary_out[epoch] = "1"
        else:
            binary_out[epoch] = "0"
    return "".join(binary_out)

# 判断一个文件是否为tar压缩文件
def is_tar_file_by_extension(file_path):
    # 检查文件扩展名是否为.tar, .tar.gz, .tar.bz2, .tgz等
    if file_path.endswith('.tar'):
        file_type = "tar"
    elif file_path.endswith('.tar.gz'):
        file_type = "gz"
    elif file_path.endswith('.tar.bz2'):
        file_type = "bz2"
    elif file_path.endswith('.tgz'):
        file_type = "tgz"
    else:
        return ""
    # 是否为一个有效的tar文件
    if tarfile.is_tarfile(file_path):
        return file_type
    else:
        return ""

class DownLoadTools(object):

    def __init__(self):
        rq = time.strftime('%Y%m%d', time.localtime(time.time()))
        self.log = HLog("main", rq)
        # 调用参数代表处理的接口类型范围
        if (len(sys.argv) - 1) == 0:
            self.log.info('未指定处理的接口范围，程序退出！')
            sys.exit(1)
        self.log.info('开始运行.......')
        self.currentPath = "./alysisFile/" + str(datetime.datetime.now().weekday()) + "day"
        gol._init()
        self.cf = configparser.ConfigParser()
        self.cf.read("./conf/deal.conf")

        self.bookdate = self.cf.get('option', 'bookdate')
        if self.bookdate == "":
            yesterday = datetime.datetime.today() + datetime.timedelta(-1)
            yesterday_format = yesterday.strftime('%Y%m%d')
            self.bookdate = yesterday_format

    def anlysisData(self,dealFileScope):
        #print(dealFileScope)
        ftpList: list = self.cf.options("source_file")
        for key in ftpList:
            cfgStr = self.cf.get("source_file", key)
            attributes = cfgStr.split(',')
            #print(attributes)
            if attributes[3] in dealFileScope:
                print("deal {0} file!".format(attributes[3]))
                self.log.info("处理 %s 文件......"%key)
                self.onecAnysis(attributes)

    def onecAnysis(self, attributes):
        tmpDir = self.cf.get("option", "tmpDir")
        sourceRoot = self.cf.get("option", "source_file")
        #cfgStr = self.cf.get("source_file", key)
        #attributes = cfgStr.split(',')
        sCurrentPath: str = sourceRoot + attributes[0]
        #print("sCurrentPath:%s"%sCurrentPath)
        # 解压源文件zip
        for root, ds, fs in os.walk(sCurrentPath):
            if root == sCurrentPath:
                for f in fs:
                    if f.find(".zip") >= 0:
                        # if f.find(self.bookdate):
                        self.log.info(f)
                        zipf = None
                        try:
                            zipf = zipfile.ZipFile(root + f, 'r')
                        except Exception as e:

                            isExists = os.path.exists(tmpDir + self.bookdate)
                            if not isExists:
                                os.makedirs(tmpDir + self.bookdate)
                            shutil.copy(root + f, tmpDir + self.bookdate)
                            os.remove(root + f)

                            self.log.error("error" + f + str(e))
                        if zipf is None:
                            self.log.error(f)
                            continue
                        for file in zipf.namelist():
                            try:
                                zipf.extract(file, sCurrentPath)
                                # 20250223 检查文件是否已解压 begin
                                file_path = os.path.join(sCurrentPath, file)
                                if not os.path.exists(file_path):
                                    self.log.error("error:" + f + "下的" + file + "文件未成功解压")
                                    continue
                                # end
                                # 20250223 增加对tar文件的解压处理 begin
                                ## 根据扩展名，这是一个可能的tar打包文件
                                if is_tar_file_by_extension(file_path) == "tar":
                                    # 解压tar文件
                                    with tarfile.open(file_path, 'r') as tar:
                                        tar.extractall(path=sCurrentPath)
                                    # 删除tar文件
                                    os.remove(file_path)
                                elif is_tar_file_by_extension(file_path) == "gz":
                                    # 解压tar.gz文件
                                    with tarfile.open(file_path, 'r:gz') as tar:
                                        tar.extractall(path=sCurrentPath)
                                    # 删除tar文件
                                    os.remove(file_path)
                                elif is_tar_file_by_extension(file_path) == "bz2":
                                    # 解压tar.bz2文件
                                    with tarfile.open(file_path, 'r:bz2') as tar:
                                        tar.extractall(path=sCurrentPath)
                                    # 删除tar文件
                                    os.remove(file_path)
                                elif is_tar_file_by_extension(file_path) == "tgz":
                                    # 解压tar.bz2文件
                                    with tarfile.open(file_path, 'r:tgz') as tar:
                                        tar.extractall(path=sCurrentPath)
                                    # 删除tar文件
                                    os.remove(file_path)
                                # end

                            except Exception as e:
                                self.log.error("error:" + f + str(e))
                        zipf.close()

                        isExists = os.path.exists(tmpDir + self.bookdate)
                        if not isExists:
                            os.makedirs(tmpDir + self.bookdate)
                        shutil.copy(root + f, tmpDir + self.bookdate)
                        os.remove(root + f)

        aCurrentPath = self.currentPath + "/" + attributes[0]
        # 清空解析后csv并创建
        #print("aCurrentPath:%s"%aCurrentPath)
        shutil.rmtree(aCurrentPath)
        os.mkdir(aCurrentPath)

        dbName = ""
        if attributes[3] == "sc":
            gol.set_value('dbName', "transaction_datasc")
            dbName = "transaction_datasc"
        elif attributes[3] == "lc":
            gol.set_value('dbName', "transaction_datalc")
            dbName = "transaction_datalc"
        elif attributes[3] == "acc":
            gol.set_value('dbName', "transaction_dataacc")
            dbName = "transaction_dataacc"

        self.log.info("处理" + sCurrentPath)
        if os.path.exists(sCurrentPath):
            if attributes[6] is None or len(attributes[6]) == 0:
                eval(attributes[5])(sCurrentPath, aCurrentPath, attributes[4], attributes[3], dbName)
            else:
                eval(attributes[5])(sCurrentPath, aCurrentPath, attributes[4], attributes[3], dbName, attributes[6].replace('|',','))

        if attributes[3] == "acc":
            if attributes[6] is None or len(attributes[6]) == 0:
                pass
            else:
                self.reconciliation_finnal(attributes[6])
                self.log.info("对账统计成功")

        # 清空源文件夹文件
        del_file(sCurrentPath)
        self.log.info("清空源文件夹文件成功")

    # 清空表数据14
    def removeDataBase(self, name):
        global mysql
        #    array = []
        #    i = 0
        #    for i in range(0, 6):
        #        array.append('ReconciliationCleanData_{0}'.format(i))

        gol.set_value('dbName', name)
        inSqlList = []

        if name in ("transaction_datalc", "transaction_datasc"):
            sql = "select CONCAT('TRUNCATE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,';') from information_schema" \
                  ".tables " \
                  "where TABLE_SCHEMA in ('" + name + "');"
            listSql = mysql.select(sql)

            for t in listSql:
                for sqlTmp in t:
                    ## Eric 2021
                    ## if (sqlTmp.find("fare_statistics") >= 0) or (sqlTmp.find("big_data_statistics") >= 0):
                    if (sqlTmp.find("fare_statistics") >= 0):
                        pass
                    else:
                        inSqlList.append(sqlTmp)
        else:
            analyList: list = self.cf.options("source_file")
            for key in analyList:
                cfgStr = self.cf.get("source_file", key)
                attributes = cfgStr.split(',')
                if attributes[3] == "acc":
                    if attributes[6] is None or len(attributes[6]) == 0:
                        pass
                    else:
                        analyTuple = attributes[6].replace('|', ',')
                        # print(analyTuple)
                        if analyTuple is None or len(analyTuple) == 0:
                            print('处理的acc对账文件，未定义处理文件类型')
                            self.log.error('处理的acc对账文件，未定义处理文件类型,处理前清除数据失败!')
                            continue

                        array = const.DZTABLELIST
                        for inDict in array:
                            if inDict['filetype'] in analyTuple:
                                for tablename in inDict["tablelist"]:
                                    sql = "TRUNCATE TABLE transaction_dataacc.{0};".format(tablename)
                                    # print(sql)
                                    inSqlList.append(sql)

        mysql.executeAll(inSqlList)

    def removeLastDayFromSql(self,dealFileScope):
        analyList: list = self.cf.options("source_file")
        for key in analyList:
            cfgStr = self.cf.get("source_file", key)
            attributes = cfgStr.split(',')
            if attributes[3] in dealFileScope:
                if attributes[3] == "lc":
                    self.removeDataBase("transaction_datalc")
                elif attributes[3] == "sc":
                    self.removeDataBase("transaction_datasc")
                elif attributes[3] == "acc":
                    self.removeDataBase("transaction_dataacc")

    def statisticsToSql(self):
        global mysql
        with open('conf/sclc.csv', 'r', encoding='UTF-8') as f:
            reader = csv.reader(f)
            count = 0
            for row in reader:

                if count < 1:
                    count = count + 1
                    continue

                columns = ""
                columnsList = []
                for index in range(len(row)):
                    if index == 0:
                        continue
                    column = row[index]
                    if column != "" and index < (len(row) - 2):
                        columns = columns + column + ","
                        columnsList.append(index)

                if row[len(row) - 2] == "":
                    cardid = "*"
                else:
                    cardid = row[len(row) - 2]
                columnsList.append(13)

                if row[len(row) - 1] == "":
                    txnamt = " "
                else:
                    txnamt = ",SUM(case when %s > 4000000000 then  - (%s^4294967295+ 1) " \
                             "ELSE %s END) AS txnamtall" % (row[len(row) - 1], row[len(row) - 1], row[len(row) - 1])
                    columnsList.append(14)

                # between = self.cf.get('mysql', 'statistics_between')
                # if between == "":
                #     yesterday = datetime.today() + timedelta(-1)
                #     yesterday_format = yesterday.strftime('%Y%m%d')
                #     between = row[1] + " BETWEEN " + yesterday_format + " AND " + yesterday_format + " "
                # else:
                #     between = row[1] + " " + between

                if row[0] == "txnproductpursecompensationfare":
                    print(1)

                sql = "SELECT " + columns + "COUNT(" + cardid + ") AS carnum" + txnamt + " FROM " + \
                      row[0] + " GROUP BY " + columns[:-1]
                re = mysql.select(sql)

                sqlColumn = ["bookdate", "occurrence_date", "lineid", "station_number", "paymethod", "equipmenttype",
                             "station_equipment_number", "posoperid", "udtype", "udsubtype", "producttype",
                             "crdmKnd", "crdSKnd", "carnum", "txnamtall"]
                columnStr = ""
                temIndex = 0
                for sIndex, columnIndex in enumerate(columnsList):
                    columnStr = columnStr + sqlColumn[columnIndex] + ","
                    if sqlColumn[columnIndex] == "station_equipment_number":
                        temIndex = sIndex

                sqllist = []

                for once in re:
                    vStr = ""
                    for ssindex, v in enumerate(once):
                        # if ssindex == (len(once) - 1):
                        # print(v)

                        # if len(txnamt) > 5 and ssindex == (len(once) - 1) and int(v) > 4000000000:
                        #     print(v)
                        #     a = int(v)
                        #     b = str(bin(a)).lstrip("0b")
                        #     b = reverse(b)
                        #     b = add_1(b)
                        #     b = -int(b, 2)
                        #
                        #     vStr = vStr + "\"" + str(b) + "\","
                        #     continue
                        if ssindex == temIndex and int(v) < 10:
                            vStr = vStr + "\"0" + str(v) + "\","
                        else:
                            vStr = vStr + "\"" + str(v) + "\","
                    sql = "INSERT INTO big_data_statistics ( bookdate," + columnStr[
                                                                          :-1] + ") VALUES (" + self.bookdate + "," + vStr[
                                                                                                                      :-1] + ")"
                    sqllist.append(sql)
                mysql.executeAll(sqllist)
                count = count + 1

    def finalStatistics(self, name):
        global mysql
        sql = "INSERT INTO fare_tmp (bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,offset_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall) " \
              "SELECT bookdate,trim(replace(occurrence_date,'\t','')),lineid,CAST(station_number AS SIGNED) as station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd,SUM(carnum),0,0,0,0,SUM(txnamtall),0,0,0,0 " \
              "FROM big_data_statistics WHERE ((udtype = 8 AND udsubtype =15 ) OR (udtype = 3 AND udsubtype = 3 ) OR (udtype = 3 AND udsubtype = 2 ) OR (udtype = 3 AND udsubtype = 1 ) OR (udtype = 3 AND udsubtype = 121 )) AND bookdate = " + self.bookdate + \
              " GROUP BY bookdate,occurrence_date,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd"
        mysql.executeAll([sql])

        sql = "INSERT INTO fare_tmp (bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,offset_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall) " \
              "SELECT bookdate,trim(replace(occurrence_date,'\t','')),lineid,CAST(station_number AS SIGNED) as station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd,0,0,SUM(carnum),0,0,0,SUM(txnamtall),0,0,0 " \
              "FROM big_data_statistics WHERE ((udtype = 8 AND udsubtype =14 ) OR (udtype = 3 AND udsubtype = 12 ) OR (udtype = 3 AND udsubtype = 11 ) OR (udtype = 3 AND udsubtype = 10 )) AND bookdate = " + self.bookdate + \
              " GROUP BY bookdate,occurrence_date,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd"
        mysql.executeAll([sql])

        sql = "INSERT INTO fare_tmp (bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,offset_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall) " \
              "SELECT bookdate,trim(replace(occurrence_date,'\t','')),lineid,CAST(station_number AS SIGNED) as station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd,0,0,0,SUM(carnum),0,0,0,SUM(txnamtall),0 ,0 " \
              "FROM big_data_statistics WHERE ((udtype = 33 AND udsubtype =17 ) OR (udtype = 34 AND udsubtype = 17 ) OR (udtype = 35 AND udsubtype = 17 ) or (udtype = 17 AND udsubtype =33 ) OR (udtype = 17 AND udsubtype = 34 ) OR (udtype = 17 AND udsubtype = 33 ) OR (udtype = 3 AND udsubtype = 120 ) OR (udtype = 3 AND udsubtype = 119 ) OR (udtype = 3 AND udsubtype = 118 )) AND bookdate = " + self.bookdate + \
              " GROUP BY bookdate,occurrence_date,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd "
        mysql.executeAll([sql])

        sql = "INSERT INTO fare_tmp (bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,offset_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall) " \
              "SELECT bookdate,trim(replace(occurrence_date,'\t','')),lineid,CAST(station_number AS SIGNED) as station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd,0,SUM(carnum),0,0,0,0,0,0,SUM(txnamtall),0 " \
              "FROM big_data_statistics WHERE ((udtype = 3 AND udsubtype =28 ) OR (udtype = 3 AND udsubtype = 27 ) OR (udtype = 3 AND udsubtype = 26 ) OR (udtype = 1 AND udsubtype = 80 ) OR (udtype = 1 AND udsubtype = 81 )OR (udtype = 80 AND udsubtype = 1 ) OR (udtype = 81 AND udsubtype = 1 )) AND bookdate = " + self.bookdate + \
              " GROUP BY bookdate,occurrence_date,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd "
        mysql.executeAll([sql])
        ## Eric 20211113 append '0316','0317','0318','0379','0380','0381'
        """
        sql = "INSERT INTO fare_tmp (bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,offset_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall) " \
              "SELECT bookdate,lineid,CAST(station_number AS SIGNED) as station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd,0,0,0,0,SUM(carnum),0,0,0,0,SUM(txnamtall) " \
              "FROM big_data_statistics WHERE ((udtype = 1 AND udsubtype =33 ) OR (udtype = 1 AND udsubtype = 32 )) AND bookdate = " + self.bookdate + \
              " GROUP BY bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd "
        """
        sql = "INSERT INTO fare_tmp (bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,offset_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall) " \
              "SELECT bookdate,trim(replace(occurrence_date,'\t','')),lineid,CAST(station_number AS SIGNED) as station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd,0,0,0,0,SUM(carnum),0,0,0,0,SUM(txnamtall) " \
              "FROM big_data_statistics WHERE ((udtype = 1 AND udsubtype =33 ) OR (udtype = 1 AND udsubtype = 32 ) OR (udtype = 3 AND udsubtype = 16 ) OR (udtype = 3 AND udsubtype = 17 ) OR (udtype = 3 AND udsubtype = 18 ) OR (udtype = 3 AND udsubtype = 79 ) OR (udtype = 3 AND udsubtype = 80 ) OR (udtype = 3 AND udsubtype = 81 )) AND bookdate = " + self.bookdate + \
              " GROUP BY bookdate,occurrence_date,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd "
        mysql.executeAll([sql])

        ## Eric 20211116 for sale_txnamtall - offset
        """
        sql = "INSERT INTO subway." + name + " (bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall,offset_carnum)" \
                                             " SELECT bookdate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),CAST(LPAD(trim(replace(station_number,'\t','')),2,0) AS SIGNED),paymethod,equipmenttype,case equipmenttype when 1 then  concat('V',LPAD(station_equipment_number,3,0)) when 2 then concat('BOM',LPAD(station_equipment_number,3,0)) end ,trim(replace(posoperid,'\t','')),productType,productSubType,sum(sale_carnum),sum(refund_carnum),sum(recharge_carnum),sum(supplement_carnum),sum(sale_txnamtall),sum(recharge_txnamtall),sum(supplement_txnamtall),sum(refund_txnamtall),sum(offset_txnamtall),sum(offset_carnum) " \
                                             " FROM fare_tmp where bookdate = %s and (lineid = 2 or lineid = 3 or lineid = 14 or lineid = 37) " \
                                             " GROUP BY bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType" % (
                  self.bookdate)

        mysql.executeAll([sql])

        sql = "INSERT INTO subway." + name + " (bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall,offset_carnum)" \
                                             " SELECT bookdate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),CAST(LPAD(trim(replace(station_number,'\t','')),2,0) AS SIGNED),paymethod,case equipmenttype when 7 then 2 when 1 then 1 end,case equipmenttype when 1 then  concat('V',LPAD(station_equipment_number,3,0)) when 7 then concat('BOM',LPAD(station_equipment_number,3,0)) end ,trim(replace(posoperid,'\t','')),productType,productSubType,sum(sale_carnum),sum(refund_carnum),sum(recharge_carnum),sum(supplement_carnum),sum(sale_txnamtall),sum(recharge_txnamtall),sum(supplement_txnamtall),sum(refund_txnamtall),sum(offset_txnamtall),sum(offset_carnum) " \
                                             " FROM fare_tmp where bookdate = %s and (lineid  = 6) " \
                                             " GROUP BY bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType" % (
                  self.bookdate)

        mysql.executeAll([sql])

        sql = "INSERT INTO subway." + name + " (bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall,offset_carnum)" \
                                             " SELECT bookdate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),CAST(LPAD(trim(replace(station_number,'\t','')),2,0) AS SIGNED),paymethod,equipmenttype,case equipmenttype when 1 then  concat('V',LPAD(station_equipment_number-60,3,0)) when 2 then concat('BOM',LPAD(station_equipment_number-90,3,0)) end ,trim(replace(posoperid,'\t','')),productType,productSubType,sum(sale_carnum),sum(refund_carnum),sum(recharge_carnum),sum(supplement_carnum),sum(sale_txnamtall),sum(recharge_txnamtall),sum(supplement_txnamtall),sum(refund_txnamtall),sum(offset_txnamtall),sum(offset_carnum) " \
                                             " FROM fare_tmp where bookdate = %s and (lineid = 1 or lineid = 4)" \
                                             " GROUP BY bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType" % (
                  self.bookdate)
        mysql.executeAll([sql])

        sql = "INSERT INTO subway." + name + " (bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall,offset_carnum)" \
                                             " SELECT bookdate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),CAST(LPAD(trim(replace(station_number,'\t','')),2,0) AS SIGNED),paymethod,equipmenttype,case equipmenttype when 1 then  concat('V',LPAD(station_equipment_number-60,3,0)) when 2 then concat('BOM',LPAD(station_equipment_number-190,3,0)) end ,trim(replace(posoperid,'\t','')),productType,productSubType,sum(sale_carnum),sum(refund_carnum),sum(recharge_carnum),sum(supplement_carnum),sum(sale_txnamtall),sum(recharge_txnamtall),sum(supplement_txnamtall),sum(refund_txnamtall),sum(offset_txnamtall),sum(offset_carnum) " \
                                             " FROM fare_tmp where bookdate = %s and (lineid = 5 or lineid = 16)" \
                                             " GROUP BY bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType" % (
                  self.bookdate)
        mysql.executeAll([sql])
        """
        sql = "INSERT INTO subway." + name + " (bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall,offset_carnum)" \
                                             " SELECT bookdate,tradeDate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),CAST(LPAD(trim(replace(station_number,'\t','')),2,0) AS SIGNED),paymethod,equipmenttype,case equipmenttype when 1 then  concat('V',LPAD(station_equipment_number,3,0)) when 2 then concat('BOM',LPAD(station_equipment_number,3,0)) end ,trim(replace(posoperid,'\t','')),productType,productSubType,sum(sale_carnum - offset_carnum),sum(refund_carnum),sum(recharge_carnum),sum(supplement_carnum),sum(sale_txnamtall - offset_txnamtall),sum(recharge_txnamtall),sum(supplement_txnamtall),sum(refund_txnamtall),sum(offset_txnamtall),sum(offset_carnum) " \
                                             " FROM fare_tmp where bookdate = %s and (lineid = 2 or lineid = 3 or lineid = 14 or lineid = 37 or lineid = 36 ) " \
                                             " GROUP BY bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType" % (
                  self.bookdate)

        mysql.executeAll([sql])
        # 20250108 begin 故障退款，售卖 - 故障退款
        """
        sql = "INSERT INTO subway." + name + " (bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall,offset_carnum)" \
                                             " SELECT bookdate,tradeDate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),CAST(LPAD(trim(replace(station_number,'\t','')),2,0)  AS SIGNED),paymethod,case equipmenttype when 7 then 2 when 1 then 1  else equipmenttype end,case equipmenttype when 1 then  concat('V',LPAD(station_equipment_number,3,0)) when 3 then  concat('STM',LPAD(station_equipment_number,3,0)) when 7 then concat('BOM',LPAD(station_equipment_number,3,0)) when 2 then concat('BOM',LPAD(station_equipment_number,3,0))end ,trim(replace(posoperid,'\t','')),productType,productSubType,sum(sale_carnum - offset_carnum),sum(refund_carnum),sum(recharge_carnum),sum(supplement_carnum),sum(sale_txnamtall - offset_txnamtall),sum(recharge_txnamtall),sum(supplement_txnamtall),sum(refund_txnamtall),sum(offset_txnamtall),sum(offset_carnum) " \
                                             " FROM fare_tmp where bookdate = %s and (lineid  = 6 or lineid  = 9) " \
                                             " GROUP BY bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType" % (
                  self.bookdate)
        """
        sql = "INSERT INTO subway." + name + " (bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall,offset_carnum)" \
                                             " SELECT bookdate,tradeDate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),CAST(LPAD(trim(replace(station_number,'\t','')),2,0)  AS SIGNED),paymethod,case equipmenttype when 7 then 2 when 1 then 1  else equipmenttype end,case equipmenttype when 1 then  concat('V',LPAD(station_equipment_number,3,0)) when 3 then  concat('STM',LPAD(station_equipment_number,3,0)) when 7 then concat('BOM',LPAD(station_equipment_number,3,0)) when 2 then concat('BOM',LPAD(station_equipment_number,3,0))end ,trim(replace(posoperid,'\t','')),productType,productSubType,case equipmenttype when 2 then sum(sale_carnum)	else sum(sale_carnum) end ,sum(refund_carnum),sum(recharge_carnum),sum(supplement_carnum),case equipmenttype when 2 then sum(sale_txnamtall - offset_txnamtall - refund_txnamtall) else sum(sale_txnamtall - offset_txnamtall) end,sum(recharge_txnamtall),sum(supplement_txnamtall),sum(refund_txnamtall),sum(offset_txnamtall),sum(offset_carnum)  " \
                                             " FROM fare_tmp where bookdate = %s and (lineid  = 6 or lineid  = 9) " \
                                             " GROUP BY bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType" % (
                  self.bookdate)
        # 20250108 end

        mysql.executeAll([sql])

        sql = "INSERT INTO subway." + name + " (bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall,offset_carnum)" \
                                             " SELECT bookdate,tradeDate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),CAST(LPAD(trim(replace(station_number,'\t','')),2,0)  AS SIGNED),paymethod,equipmenttype,case equipmenttype when 1 then  concat('V',LPAD(station_equipment_number-60,3,0)) when 2 then concat('BOM',LPAD(station_equipment_number-90,3,0)) end ,trim(replace(posoperid,'\t','')),productType,productSubType,sum(sale_carnum - offset_carnum),sum(refund_carnum),sum(recharge_carnum),sum(supplement_carnum),sum(sale_txnamtall - offset_txnamtall),sum(recharge_txnamtall),sum(supplement_txnamtall),sum(refund_txnamtall),sum(offset_txnamtall),sum(offset_carnum) " \
                                             " FROM fare_tmp where bookdate = %s and (lineid = 1 or lineid = 4)" \
                                             " GROUP BY bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType" % (
                  self.bookdate)
        mysql.executeAll([sql])

        sql = "INSERT INTO subway." + name + " (bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall,offset_carnum)" \
                                             " SELECT bookdate,tradeDate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),CAST(LPAD(trim(replace(station_number,'\t','')),2,0) AS SIGNED),paymethod,equipmenttype,case equipmenttype when 1 then  concat('V',LPAD(station_equipment_number-60,3,0)) when 2 then concat('BOM',LPAD(station_equipment_number-190,3,0)) end ,trim(replace(posoperid,'\t','')),productType,productSubType,sum(sale_carnum - offset_carnum),sum(refund_carnum),sum(recharge_carnum),sum(supplement_carnum),sum(sale_txnamtall - offset_txnamtall),sum(recharge_txnamtall),sum(supplement_txnamtall),sum(refund_txnamtall),sum(offset_txnamtall),sum(offset_carnum) " \
                                             " FROM fare_tmp where bookdate = %s and (lineid = 5 or lineid = 16)" \
                                             " GROUP BY bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType" % (
                  self.bookdate)
        mysql.executeAll([sql])

    def sc_out_statistics(self,sclc):
        global mysql
        gol.set_value('dbName', "subway")

        ## Eric 20211110 append udtype and udsubtype
        """
        sql = "INSERT INTO subway.sc_out_statistics (bookdate,lineid,station_number,udType,udSubType,productType,productSubType,outCount,amount) " \
              "SELECT bookdate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),CAST(LPAD(trim(replace(station_number,'\t','')),2,0) AS SIGNED),udtype,udsubtype,crdmKnd,crdSKnd,SUM(carnum),SUM(txnamtall) " \
              "FROM transaction_datasc.big_data_statistics WHERE ((udtype = 33 AND udsubtype = 13 ) " \
              " OR (udtype = 33 AND udsubtype = 15 ) OR (udtype = 34 AND udsubtype = 13 ) " \
              " OR (udtype = 34 AND udsubtype = 15 ) OR (udtype = 35 AND udsubtype = 13 ) " \
              " OR (udtype = 35 AND udsubtype = 15 ) OR (udtype = 3 AND udsubtype = 93 ) " \
              "OR (udtype = 3 AND udsubtype = 92 ) " \
              "OR (udtype = 3 AND udsubtype = 91 ) OR (udtype = 35 AND udsubtype = 113 ) " \
              "OR (udtype = 3 AND udsubtype = 115 ) OR (udtype = 35 AND udsubtype = 117 )) AND " \
              "bookdate = %s GROUP BY bookdate,lineid,station_number,crdmKnd,crdSKnd" % self.bookdate
        """
        sql =""
        if sclc == "sc":
            sql = "INSERT INTO subway.sc_out_statistics (bookdate,lineid,station_number,udType,udSubType,productType,productSubType,outCount,amount) " \
                  "SELECT bookdate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),CAST(LPAD(trim(replace(station_number,'\t','')),2,0) AS SIGNED),udtype,udsubtype,crdmKnd,crdSKnd,SUM(carnum),SUM(txnamtall) " \
                  "FROM transaction_datasc.big_data_statistics WHERE ((udtype = 33 AND udsubtype = 13 ) " \
                  " OR (udtype = 33 AND udsubtype = 15 ) OR (udtype = 34 AND udsubtype = 13 ) " \
                  " OR (udtype = 34 AND udsubtype = 15 ) OR (udtype = 35 AND udsubtype = 13 ) " \
                  " OR (udtype = 35 AND udsubtype = 15 ) OR (udtype = 3 AND udsubtype = 93 ) " \
                  "OR (udtype = 3 AND udsubtype = 92 ) " \
                  "OR (udtype = 3 AND udsubtype = 91 ) OR (udtype = 35 AND udsubtype = 113 ) " \
                  "OR (udtype = 3 AND udsubtype = 115 ) OR (udtype = 35 AND udsubtype = 117 )) AND " \
                  "bookdate = %s GROUP BY bookdate,lineid,station_number,udType,udSubType,crdmKnd,crdSKnd" % self.bookdate
        if sclc == "lc":
            ## Eric append next lc_out_statistics
            sql = "INSERT INTO subway.lc_out_statistics (bookdate,lineid,station_number,udType,udSubType,productType,productSubType,outCount,amount) " \
                  "SELECT bookdate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),CAST(LPAD(trim(replace(station_number,'\t','')),2,0) AS SIGNED),udtype,udsubtype,crdmKnd,crdSKnd,SUM(carnum),SUM(txnamtall) " \
                  "FROM transaction_datalc.big_data_statistics WHERE ((udtype = 33 AND udsubtype = 13 ) " \
                  " OR (udtype = 33 AND udsubtype = 15 ) OR (udtype = 34 AND udsubtype = 13 ) " \
                  " OR (udtype = 34 AND udsubtype = 15 ) OR (udtype = 35 AND udsubtype = 13 ) " \
                  " OR (udtype = 35 AND udsubtype = 15 ) OR (udtype = 3 AND udsubtype = 93 ) " \
                  "OR (udtype = 3 AND udsubtype = 92 ) " \
                  "OR (udtype = 3 AND udsubtype = 91 ) OR (udtype = 35 AND udsubtype = 113 ) " \
                  "OR (udtype = 3 AND udsubtype = 115 ) OR (udtype = 35 AND udsubtype = 117 )) AND " \
                  "bookdate = %s GROUP BY bookdate,lineid,station_number,udType,udSubType,crdmKnd,crdSKnd" % self.bookdate

        mysql.executeAll([sql])

    def update_day_cell(self):
        sql = "update subway.sw_st_stock AS ss " \
              "set ss. AGM_RECOVERY = (select  sum(ifnull(outCount,0)) FROM  subway.sc_out_statistics where bookdate='%s'  and lineid = ss.LINE_CODE and station_number= ss.STATION_CODE and (producttype = '3' or producttype = '1' or producttype = '12' ))," \
              "ss.BOX_OFFICE_SALE = (select  sum(ifnull(sale_carnum,0)) from  subway.sc_fare_statistics where bookdate='%s'  and lineid=ss.LINE_CODE and station_number= ss.STATION_CODE and (producttype = 3 or producttype =1 or producttype = 12 )  and equipmenttype=2)," \
              "ss.TVM_SELL_OUT = (select  sum(ifnull(sale_carnum,0))  from  subway.sc_fare_statistics where bookdate='%s'  and lineid=ss.LINE_CODE and station_number=ss.STATION_CODE and producttype=3  and equipmenttype=1) " \
              "where STATISTICS_DATE='%s' and del_flag=0 and TICKET_TYPE_PARAM_EN='PTDCP'" % (
                  self.bookdate, self.bookdate, self.bookdate, self.bookdate)

        mysql.executeAll([sql])

    def updataToFinal(self):
        global mysql
        gol.set_value('dbName', "subway")

        # 钱箱入账
        #self.box5109ToSubway()
        #self.log.info("钱箱入账")

        # sw_au_tvm_amount_defference
        #self.updateFinalForType231("sc", "2")
        #self.updateFinalForType231("lc", "3")
        # self.updateFinalForType231("acc", "4")
        # 不要了 self.update5109For231()

        # sw_au_tvm_box_amount_difference
        #self.updateFinalForType232()

        # SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE
        #self.updateFinalForType2314("sc", "1")
        #self.updateFinalForType2314("lc", "2")
        # self.updateFinalForType2314("acc", "3")

        # sw_au_conductor_hierarchy_amount_difference
        self.updateFinalForType2315("sc_fare_statistics", "lc_fare_statistics", "1")
        # self.updateFinalForType2315("lc_fare_statistics", "acc_fare_statistics", "2")

        # SW_AU_ITVM_ONLINE_AMOUNT_HIERARCHY_DIFFERENCE
        # self.updateFinalForType239("sc", 1, 2)
        # self.updateFinalForType239("lc", 1, 3)
        # self.updateFinalForType239("acc", 1, 4)
        self.updateFinalForType239("sc_fare_statistics", "lc_fare_statistics", "2", "3")
        # self.updateFinalForType239("lc_fare_statistics", "acc_fare_statistics", "3", "4")

    def updataToFinalACC(self):
        global mysql
        gol.set_value('dbName', "subway")
        self.updateFinalForType231("acc", "4")
        self.updateFinalForType2314("acc", "3")

        self.updateFinalForType2315("lc_fare_statistics", "acc_fare_statistics", "2")

        # SW_AU_ITVM_ONLINE_AMOUNT_HIERARCHY_DIFFERENCE
        self.updateFinalForType239("lc_fare_statistics", "acc_fare_statistics", "3", "4")

    def updateFinalForType231(self, typeName, typeID):
        global mysql
        gol.set_value('dbName', "subway")

        sqlList = []
        # 叠加
        # 现金差异
        ## Eric RIGHT JOIN 改为 LEFT JOIN
        """ 
        sql = "UPDATE sw_au_tvm_amount_defference a " \
              "JOIN (SELECT id, SUM(sm) sm, SUM(cz) cz, SUM(tk) tk " \
              "FROM (SELECT * " \
              "FROM sw_au_tvm_amount_defference a " \
              "RIGHT JOIN (SELECT bookdate,lineid,station_number,equipmenttype,station_equipment_number,posoperid,sale_txnamtall + supplement_txnamtall AS sm,recharge_txnamtall AS cz,refund_txnamtall AS tk " \
              "FROM %s_fare_statistics " \
              "WHERE equipmenttype = 1 AND (paymethod = 0 OR paymethod = 1 OR paymethod = 10 OR  paymethod is NULL OR paymethod LIKE '%%313000000000000000000000%%') " \
              ") AS b ON a.LINE_CODE = b.lineid AND a.STATION_CODE = b.station_number AND a.TVM_CODE = b.station_equipment_number AND (b.bookdate BETWEEN a.INCOME_SETTLE_BEGIN_TIME AND a.INCOME_SETTLE_END_TIME) " \
              "WHERE a.RECORD_TIME = %s ) c " \
              "WHERE AUDIT_STATUS = 0 AND DEL_FLAG = 0 " \
              "GROUP BY id) b ON a.ID = b.id " \
              "SET " \
              "%s_CASH_SELL_TICKET = b.sm," \
              "%s_CASH_RECHARGE = b.cz," \
              "%s_CASH_RETURN = b.tk " \
              "WHERE a.RECORD_TIME = %s AND a.AUDIT_STATUS = 0 AND a.DEL_FLAG = 0 " \
              % (typeName, self.bookdate, typeName, typeName, typeName, self.bookdate)
        """
        sql = "UPDATE sw_au_tvm_amount_defference a " \
              "JOIN (SELECT id, SUM(ifnull(sm,0)) sm, SUM(ifnull(cz,0)) cz, SUM(ifnull(tk,0)) tk " \
              "FROM (SELECT a.*,b.bookdate,b.lineid,b.station_number,b.equipmenttype,b.station_equipment_number,b.posoperid,b.sale_txnamtall + b.supplement_txnamtall AS sm,b.recharge_txnamtall AS cz,b.refund_txnamtall AS tk  " \
              "FROM sw_au_tvm_amount_defference a " \
              "LEFT JOIN %s_fare_statistics b " \
              " ON a.LINE_CODE = b.lineid AND a.STATION_CODE = b.station_number AND a.TVM_CODE = b.station_equipment_number AND (b.bookdate BETWEEN a.INCOME_SETTLE_BEGIN_TIME AND a.INCOME_SETTLE_END_TIME) " \
              " WHERE a.RECORD_TIME = %s " \
              " AND b.equipmenttype = 1 AND (b.paymethod = 0 OR b.paymethod = 1 OR b.paymethod = 10 OR  b.paymethod is NULL OR b.paymethod LIKE '%%313000000000000000000000%%') " \
              " ) c " \
              "WHERE AUDIT_STATUS = 0 AND DEL_FLAG = 0 " \
              "GROUP BY id) b ON a.ID = b.id " \
              "SET " \
              "%s_CASH_SELL_TICKET = b.sm," \
              "%s_CASH_RECHARGE = b.cz," \
              "%s_CASH_RETURN = b.tk " \
              "WHERE a.RECORD_TIME = %s AND a.AUDIT_STATUS = 0 AND a.DEL_FLAG = 0 " \
              % (typeName, self.bookdate, typeName, typeName, typeName, self.bookdate)
        sqlList.append(sql)

        sql = "UPDATE sw_au_tvm_amount_defference	" \
              "SET   DIFF_MONEY = ACTUAL_MONEY - (ifnull(%s_CASH_SELL_TICKET,0) + ifnull(%s_CASH_RECHARGE,0) + ifnull(%s_CASH_RETURN,0))," \
              "SYSTEM_MONEY = (ifnull(%s_CASH_SELL_TICKET,0) +ifnull(%s_CASH_RECHARGE,0) + ifnull(%s_CASH_RETURN,0))" \
              "WHERE RECORD_TIME = %s AND  DATA_SOURCE = %s AND AUDIT_STATUS = 0 AND DEL_FLAG = 0 " \
              % (typeName, typeName, typeName, typeName, typeName, typeName, self.bookdate, typeID)
        sqlList.append(sql)

        mysql.executeAll(sqlList)

    def update5109For231(self):
        global mysql
        gol.set_value('dbName', "subway")
        sqlList = []
        sql = "SELECT bookdate,lineid,station_number,station_equipment_number,SUM(amount) " \
              "FROM sc_tvm_coin_opt " \
              " WHERE bookdate = %s " \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number" % (self.bookdate)
        fc = mysql.select(sql)

        if fc is None:
            return
        for data in fc:
            sql = "UPDATE sw_au_tvm_amount_defference	" \
                  "SET   ACTUAL_MONEY =  ifnull(ACTUAL_MONEY,0) + %d  " \
                  "WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = \'%s\'  AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND  DATA_SOURCE = 'sc' AND AUDIT_STATUS = 0" \
                  % (decimal.Decimal(data[4]), data[1], data[2], data[3], data[0])
            sqlList.append(sql)
        mysql.executeAll(sqlList)

    def updateFinalForType232(self):
        global mysql
        gol.set_value('dbName', "subway")

        sqlList = []
        # 20210917 Eric
        """
        sql = "SELECT bookdate,lineid,station_number,station_equipment_number,coin_number,opt_type,amount,cash_box_type " \
              " FROM subway.sc_tvm_coin_opt " \
              " WHERE opt_type = 2 and  bookdate = %s " % self.bookdate

        fc = mysql.select(sql)
        if fc is None:
            return
        for data in fc:

            if int(data[5]) == 2:
                sql = "UPDATE subway.sw_au_tvm_box_amount_difference " \
                      "set SC_UNLOAD_AMOUNT =  ifnull(SC_UNLOAD_AMOUNT,0) + %d " \
                      " WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = '%s' AND CASH_BOX_TYPE = '%s' " \
                      "AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND AUDIT_STATUS = 0" \
                      % (decimal.Decimal(data[6]), data[1], data[2], data[3], data[7], data[0])
                sqlList.append(sql)
            elif int(data[5]) == 1:
                sql = "UPDATE subway.sw_au_tvm_box_amount_difference " \
                      "set SC_SUPPLEMNT_AMOUNT =  ifnull(SC_SUPPLEMNT_AMOUNT,0) + %d" \
                      " WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = '%s' AND CASH_BOX_TYPE = '%s' " \
                      "AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND AUDIT_STATUS = 0" % (
                          decimal.Decimal(data[6]), data[1], data[2], data[3], data[7], data[0])
                sqlList.append(sql)


            sql = "UPDATE subway.sw_au_tvm_box_amount_difference " \
                  " SET SC_SALE_AMOUNT =  case when CASH_BOX_TYPE = 2 or CASH_BOX_TYPE = 4 then ifnull(SC_SUPPLEMNT_AMOUNT,0) - ifnull(SC_UNLOAD_AMOUNT,0) - ifnull(SC_HAND_OUT_AMOUNT,0)" \
                  " when CASH_BOX_TYPE = 1 or CASH_BOX_TYPE = 3  then  ifnull(SC_UNLOAD_AMOUNT,0) - ifnull(SC_SUPPLEMNT_AMOUNT,0) + ifnull(SC_HAND_OUT_AMOUNT,0) END " \
                  " WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = '%s' AND CASH_BOX_TYPE = '%s' " \
                  "AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND AUDIT_STATUS = 0" % (
                      data[1], data[2], data[3], data[7], data[0])
            sqlList.append(sql)

            sql = "UPDATE subway.sw_au_tvm_box_amount_difference " \
                  "SET SYSTEM_MONEY =  ifnull(SC_UNLOAD_AMOUNT,0)," \
                  "DIFF_MONEY =   ifnull(ACTUAL_MONEY,0)  - ifnull(SC_UNLOAD_AMOUNT,0) + ifnull(HAND_OUT_MONEY,0)" \
                  " WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = '%s' AND CASH_BOX_TYPE = '%s' AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND  DATA_SOURCE = 2 AND AUDIT_STATUS = 0" \
                  % (data[1], data[2], data[3], data[7], data[0])
            sqlList.append(sql)
        """
        ## Eric 20211201 RIGHT JOIN 改为 LEFT JOIN
        """ 
        sql = "UPDATE subway.sw_au_tvm_box_amount_difference a " \
              "JOIN (SELECT ID, SUM(sm) sm " \
              "FROM (SELECT * " \
              "FROM subway.sw_au_tvm_box_amount_difference a " \
              "RIGHT JOIN (SELECT bookdate,lineid,station_number,station_equipment_number,opt_type,cash_box_type as cash_box_type1,amount sm " \
              " FROM subway.sc_tvm_coin_opt " \
              " WHERE opt_type = 2 " \
              ") AS b ON a.LINE_CODE = b.lineid AND a.STATION_CODE = b.station_number AND a.TVM_CODE = b.station_equipment_number AND a.CASH_BOX_TYPE = b.cash_box_type1 AND (b.bookdate BETWEEN a.INCOME_SETTLE_BEGIN_TIME AND a.INCOME_SETTLE_END_TIME) " \
              "WHERE a.RECORD_TIME = %s AND a.AUDIT_STATUS = 0 AND a.DEL_FLAG = 0 ) c " \
              "WHERE AUDIT_STATUS = 0 AND DEL_FLAG = 0 " \
              "GROUP BY id) b ON a.ID = b.id " \
              "SET " \
              "a.SC_UNLOAD_AMOUNT = b.sm, " \
              "a.SC_SALE_AMOUNT =  case when a.CASH_BOX_TYPE = 2 or a.CASH_BOX_TYPE = 4 then ifnull(a.SC_SUPPLEMNT_AMOUNT,0) - ifnull(b.sm,0) - ifnull(a.SC_HAND_OUT_AMOUNT,0) " \
              " when a.CASH_BOX_TYPE = 1 or a.CASH_BOX_TYPE = 3  then  ifnull(b.sm,0) - ifnull(a.SC_SUPPLEMNT_AMOUNT,0) + ifnull(a.SC_HAND_OUT_AMOUNT,0) END " \
              "WHERE a.RECORD_TIME = %s" \
              % (self.bookdate, self.bookdate)
        """
        sql = "UPDATE subway.sw_au_tvm_box_amount_difference a " \
              "JOIN (SELECT ID, SUM(ifnull(sm,0)) sm " \
              "FROM (SELECT * " \
              "FROM subway.sw_au_tvm_box_amount_difference a " \
              "LEFT JOIN (SELECT bookdate,lineid,station_number,station_equipment_number,opt_type,cash_box_type as cash_box_type1,amount sm " \
              " FROM subway.sc_tvm_coin_opt " \
              " WHERE opt_type = 2 " \
              ") AS b ON a.LINE_CODE = b.lineid AND a.STATION_CODE = b.station_number AND a.TVM_CODE = b.station_equipment_number AND a.CASH_BOX_TYPE = b.cash_box_type1 AND (b.bookdate BETWEEN a.INCOME_SETTLE_BEGIN_TIME AND a.INCOME_SETTLE_END_TIME) " \
              "WHERE a.RECORD_TIME = %s) c " \
              "WHERE AUDIT_STATUS = 0 AND DEL_FLAG = 0 " \
              "GROUP BY id) b ON a.ID = b.id " \
              "SET " \
              "a.SC_UNLOAD_AMOUNT = b.sm, " \
              "a.SC_SALE_AMOUNT =  case when a.CASH_BOX_TYPE = 2 or a.CASH_BOX_TYPE = 4 then ifnull(a.SC_SUPPLEMNT_AMOUNT,0) - ifnull(b.sm,0) - ifnull(a.SC_HAND_OUT_AMOUNT,0) " \
              " when a.CASH_BOX_TYPE = 1 or a.CASH_BOX_TYPE = 3  then  ifnull(b.sm,0) - ifnull(a.SC_SUPPLEMNT_AMOUNT,0) + ifnull(a.SC_HAND_OUT_AMOUNT,0) END " \
              "WHERE a.RECORD_TIME = %s  AND a.AUDIT_STATUS = 0 AND a.DEL_FLAG = 0 " \
              % (self.bookdate, self.bookdate)
        sqlList.append(sql)

        sql = "UPDATE subway.sw_au_tvm_box_amount_difference a " \
              "SET SYSTEM_MONEY =  ifnull(SC_UNLOAD_AMOUNT,0)," \
              "DIFF_MONEY =   ifnull(ACTUAL_MONEY,0)  - ifnull(SC_UNLOAD_AMOUNT,0) + ifnull(HAND_OUT_MONEY,0)" \
              " WHERE a.RECORD_TIME = %s AND DATA_SOURCE = 2 AND AUDIT_STATUS = 0 AND DEL_FLAG = 0" \
              % (self.bookdate)
        sqlList.append(sql)

        mysql.executeAll(sqlList)

    def updateFinalForType2314(self, typeName, typeID):
        global mysql
        gol.set_value('dbName', "subway")

        sqlList = []

        #
        # 20210917 Eric
        """
        sql = "SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall),SUM(sale_txnamtall),SUM(supplement_txnamtall)" \
              "FROM subway.%s_fare_statistics  " \
              "WHERE equipmenttype in ('2','7')  AND (paymethod = 0 OR paymethod = 1 OR paymethod = 10) AND bookdate = %s " \
              "GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid" % (
                  typeName, self.bookdate)
        fc = mysql.select(sql)
        if fc is None:
            return
        for data in fc:
            sql = "UPDATE subway.SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE	" \
                  "SET  %s_INVEST_AMOUNT = ifnull(%s_INVEST_AMOUNT,0) + %d  " \
                  "WHERE LINE_CODE = %s AND STATION_CODE = %s AND FIND_IN_SET('%s',BOM_CODE)  AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND EQUIPMENT_NUMBER = '%s' " \
                  % (typeName, typeName, data[5], data[1], data[2], data[3], data[0], data[4])
            sqlList.append(sql)
            sql = "UPDATE subway.SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE	" \
                  "SET  %s_SELL_AMOUNT = ifnull(%s_SELL_AMOUNT,0) + %d  " \
                  "WHERE LINE_CODE = %s AND STATION_CODE = %s AND FIND_IN_SET('%s',BOM_CODE)  AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND EQUIPMENT_NUMBER = '%s' " \
                  % (typeName, typeName, data[6], data[1], data[2], data[3], data[0], data[4])
            sqlList.append(sql)
            sql = "UPDATE subway.SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE	" \
                  "SET  %s_FARETK_AMOUNT = ifnull(%s_FARETK_AMOUNT,0) + %d  " \
                  "WHERE LINE_CODE = %s AND STATION_CODE = %s AND FIND_IN_SET('%s',BOM_CODE)  AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND EQUIPMENT_NUMBER = '%s' " \
                  % (typeName, typeName, data[7], data[1], data[2], data[3], data[0], data[4])
            sqlList.append(sql)

            sql = "UPDATE subway.SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE	" \
                  "SET  DIFFERENCE_AMOUNT =  PAID_AMOUNT -  MARKET_TICKET_AMOUNT - PASSENGER_WORK_AMOUNT - SINGLE_TICKET_AMOUNT - EMERGENCY_AMOUNT - (ifnull(%s_INVEST_AMOUNT,0) + ifnull(%s_SELL_AMOUNT,0) + ifnull(%s_FARETK_AMOUNT,0))," \
                  "SYS_AMOUNT = ifnull(%s_INVEST_AMOUNT,0) + ifnull(%s_SELL_AMOUNT,0) + ifnull(%s_FARETK_AMOUNT,0)" \
                  "WHERE LINE_CODE = %s AND STATION_CODE = %s AND  FIND_IN_SET('%s',BOM_CODE) AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND EQUIPMENT_NUMBER = '%s' AND SYS_DATA_SOURCE = %s AND AUDIT_STATE = 0" \
                  % (typeName, typeName, typeName, typeName, typeName, typeName, data[1], data[2], data[3], data[0],
                     data[4], typeID)
            sqlList.append(sql)
        """
        #
        ## Eric 20211201 RIGHT JOIN 改为 LEFT JOIN
        """ 
        sql = "UPDATE SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE a " \
              "JOIN (SELECT id, SUM(sm) sm, SUM(cz) cz, SUM(tk) tk " \
              "FROM (SELECT * " \
              "FROM SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE a " \
              "RIGHT JOIN (SELECT bookdate,lineid,station_number,equipmenttype,station_equipment_number,posoperid,sale_txnamtall AS sm,recharge_txnamtall AS cz,supplement_txnamtall AS tk " \
              " FROM %s_fare_statistics " \
              " WHERE equipmenttype in ('2','7') AND (paymethod = 0 OR paymethod = 1 OR paymethod = 10 OR  paymethod is NULL OR paymethod LIKE '%%313000000000000000000000%%') " \
              ") AS b ON a.LINE_CODE = b.lineid AND a.STATION_CODE = b.station_number AND a.EQUIPMENT_NUMBER = b.posoperid AND FIND_IN_SET(b.station_equipment_number,a.BOM_CODE) AND (b.bookdate BETWEEN a.INCOME_SETTLE_BEGIN_TIME AND a.INCOME_SETTLE_END_TIME) " \
              " WHERE a.RECORD_TIME = %s AND a.AUDIT_STATE = 0 AND a.DEL_FLAG = 0 ) c " \
              " WHERE AUDIT_STATE = 0 AND DEL_FLAG = 0 " \
              " GROUP BY id) b ON a.ID = b.id " \
              " SET " \
              "%s_SELL_AMOUNT = b.sm," \
              "%s_INVEST_AMOUNT = b.cz," \
              "%s_FARETK_AMOUNT = b.tk " \
              " WHERE a.RECORD_TIME = %s" \
              % (typeName, self.bookdate, typeName, typeName, typeName, self.bookdate)
        """
        sql = "UPDATE SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE a " \
              "JOIN (SELECT id, SUM(ifnull(sm,0)) sm, SUM(ifnull(cz,0)) cz, SUM(ifnull(tk,0)) tk " \
              "FROM (SELECT a.* ,bookdate,lineid,station_number,equipmenttype,station_equipment_number,posoperid,sale_txnamtall AS sm,recharge_txnamtall AS cz,supplement_txnamtall AS tk " \
              "FROM SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE a " \
              "LEFT JOIN %s_fare_statistics b " \
              " ON a.LINE_CODE = b.lineid AND a.STATION_CODE = b.station_number AND a.EQUIPMENT_NUMBER = b.posoperid AND FIND_IN_SET(b.station_equipment_number,a.BOM_CODE) AND (b.bookdate BETWEEN a.INCOME_SETTLE_BEGIN_TIME AND a.INCOME_SETTLE_END_TIME) " \
              " WHERE a.RECORD_TIME = %s " \
              " AND b.equipmenttype in ('2','7') AND (b.paymethod = 0 OR b.paymethod = 1 OR b.paymethod = 10 OR  b.paymethod is NULL OR b.paymethod LIKE '%%313000000000000000000000%%') " \
              " ) c " \
              " WHERE AUDIT_STATE = 0 AND DEL_FLAG = 0 " \
              " GROUP BY id) b ON a.ID = b.id " \
              " SET " \
              "%s_SELL_AMOUNT = b.sm," \
              "%s_INVEST_AMOUNT = b.cz," \
              "%s_FARETK_AMOUNT = b.tk " \
              " WHERE a.RECORD_TIME = %s  AND a.AUDIT_STATE = 0 AND a.DEL_FLAG = 0 " \
              % (typeName, self.bookdate, typeName, typeName, typeName, self.bookdate)
        sqlList.append(sql)
        #print('测试:'+sql)
        sql = "UPDATE SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE	" \
              " SET DIFFERENCE_AMOUNT = PAID_AMOUNT - MARKET_TICKET_AMOUNT - PASSENGER_WORK_AMOUNT - SINGLE_TICKET_AMOUNT - EMERGENCY_AMOUNT - (ifnull(%s_INVEST_AMOUNT, 0) + ifnull(%s_SELL_AMOUNT, 0) + ifnull(%s_FARETK_AMOUNT, 0))," \
              "SYS_AMOUNT = ifnull(%s_INVEST_AMOUNT, 0) + ifnull(%s_SELL_AMOUNT, 0) + ifnull(%s_FARETK_AMOUNT, 0)" \
              " WHERE RECORD_TIME = %s AND  SYS_DATA_SOURCE = %s AND AUDIT_STATE = 0 AND DEL_FLAG = 0 " \
              % (typeName, typeName, typeName, typeName, typeName, typeName, self.bookdate, typeID)
        sqlList.append(sql)

        mysql.executeAll(sqlList)

    def updateFinalForType2315(self, tb1Name, tb2Name, typeStr):
        sqlList = []
        global mysql
        gol.set_value('dbName', "subway")
        #
        # 20211028 Eric for offset
        """
        sql = "SELECT DISTINCT * from" \
              " (SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall) AS sc_rt,SUM(sale_txnamtall) AS sc_st,SUM(supplement_txnamtall) AS sc_sut" \
              " FROM %s" \
              " WHERE equipmenttype in ('2','7') AND (paymethod = 0 OR paymethod = 1 OR paymethod = 10) AND bookdate = %s" \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid) AS t1" \
              " RIGHT  JOIN (SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall) AS lc_rt,SUM(sale_txnamtall) AS lc_st,SUM(supplement_txnamtall) AS lc_sut" \
              " FROM %s" \
              " WHERE equipmenttype in ('2','7') AND (paymethod = 0 OR paymethod = 1 OR paymethod = 10) AND bookdate = %s" \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid) AS t2 " \
              " on t1.bookdate=t2.bookdate AND t1.lineid=t2.lineid AND t1.station_number = t2.station_number" \
              " AND t1.station_equipment_number = t2.station_equipment_number AND  t1.posoperid = t2.posoperid" \
              " UNION  " \
              " SELECT * from" \
              " (SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall) AS sc_rt,SUM(sale_txnamtall) AS sc_st,SUM(supplement_txnamtall) AS sc_sut" \
              " FROM %s " \
              " WHERE equipmenttype in ('2','7') AND (paymethod = 0 OR paymethod = 1 OR paymethod = 10) AND bookdate = %s" \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid) AS t1" \
              " LEFT JOIN (SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall) AS lc_rt,SUM(sale_txnamtall) AS lc_st,SUM(supplement_txnamtall) AS lc_sut" \
              " FROM %s" \
              " WHERE equipmenttype in ('2','7') AND (paymethod = 0 OR paymethod = 1 OR paymethod = 10) AND bookdate = %s " \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid) AS t2 " \
              " on t1.bookdate=t2.bookdate AND t1.lineid=t2.lineid AND t1.station_number = t2.station_number " \
              " AND t1.station_equipment_number = t2.station_equipment_number AND  t1.posoperid = t2.posoperid" % (
                  tb1Name, self.bookdate, tb2Name, self.bookdate, tb1Name, self.bookdate, tb2Name, self.bookdate)
        """
        sql = "SELECT DISTINCT * from" \
              " (SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall) AS sc_rt,SUM(sale_txnamtall) AS sc_st,SUM(supplement_txnamtall) AS sc_sut" \
              " FROM %s" \
              " WHERE equipmenttype in ('2','7') AND (paymethod = 0 OR paymethod = 1 OR paymethod = 10) AND bookdate = %s" \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid) AS t1" \
              " RIGHT  JOIN (SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall) AS lc_rt,SUM(sale_txnamtall) AS lc_st,SUM(supplement_txnamtall) AS lc_sut" \
              " FROM %s" \
              " WHERE equipmenttype in ('2','7') AND (paymethod = 0 OR paymethod = 1 OR paymethod = 10) AND bookdate = %s" \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid) AS t2 " \
              " on t1.bookdate=t2.bookdate AND t1.lineid=t2.lineid AND t1.station_number = t2.station_number" \
              " AND t1.station_equipment_number = t2.station_equipment_number AND  t1.posoperid = t2.posoperid" \
              " UNION  " \
              " SELECT * from" \
              " (SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall) AS sc_rt,SUM(sale_txnamtall) AS sc_st,SUM(supplement_txnamtall) AS sc_sut" \
              " FROM %s " \
              " WHERE equipmenttype in ('2','7') AND (paymethod = 0 OR paymethod = 1 OR paymethod = 10) AND bookdate = %s" \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid) AS t1" \
              " LEFT JOIN (SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall) AS lc_rt,SUM(sale_txnamtall) AS lc_st,SUM(supplement_txnamtall) AS lc_sut" \
              " FROM %s" \
              " WHERE equipmenttype in ('2','7') AND (paymethod = 0 OR paymethod = 1 OR paymethod = 10) AND bookdate = %s " \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid) AS t2 " \
              " on t1.bookdate=t2.bookdate AND t1.lineid=t2.lineid AND t1.station_number = t2.station_number " \
              " AND t1.station_equipment_number = t2.station_equipment_number AND  t1.posoperid = t2.posoperid" % (
                  tb1Name, self.bookdate, tb2Name, self.bookdate, tb1Name, self.bookdate, tb2Name, self.bookdate)
        fc = mysql.select(sql)
        if fc is None:
            return
        for data in fc:
            if data[14] != data[6] or data[13] != data[5] or data[15] != data[7]:

                dataList = list(data)
                index = 0
                if data[0] is None:
                    index = 8
                    dataList[5] = 0
                    dataList[6] = 0
                    dataList[7] = 0
                if data[8] is None:
                    index = 0
                    dataList[13] = 0
                    dataList[14] = 0
                    dataList[15] = 0

                # sqlTmp = "SELECT ID FROM sw_au_center_conductor_amount_difference WHERE LINE_CODE = %s AND STATION_CODE = %s AND BOM_CODE = \'BOM%s\' AND EQUIPMENT_NUMBER = %s AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME)" % (
                # data[1 + index], data[2 + index], data[4 + index], data[5 + index], self.bookdate)
                # fctmp = mysql.select(sqlTmp)
                sqlPoser = "select user_code,nikename from sys_user where device_operate_no = '%s' and del_flag = 0" % (
                    dataList[index + 4])
                fctmp = mysql.select(sqlPoser)
                if fctmp is None or len(fctmp) == 0:
                    continue

                sql = "INSERT INTO  sw_au_conductor_hierarchy_amount_difference " \
                      "(RELATIONID,LINE_CODE,STATION_CODE,CONDUCTOR_PERSON_CODE,CONDUCTOR_PERSON_NAME,EQUIPMENT_NUMBER,BOM_CODE,COMPARATIVE_TYPE," \
                      " INVEST_AMOUNT,SELL_AMOUNT,FARETK_AMOUNT,COMPARATIVE_INVEST_AMOUNT,COMPARATIVE_SELLTK_AMOUNT,COMPARATIVE_FARETK_AMOUNT," \
                      " DIFF_INVEST_AMOUNT,DIFF_SELLTK_AMOUNT,DIFF_FARETK_AMOUNT," \
                      " ASSIST_STATE,CREATE_TIME,ASSIT_STATE,REPLACE_STATUS,SETTLE_TIME,IS_ADD_ASSIST)" \
                      " VALUES" \
                      " (%d,%s,%s,'%s','%s','%s','%s','%s',%d,%d,%d,%d,%d,%d,%d,%d,%d,%s,now(),%s,%s,%s,%s)" \
                      % (0, dataList[1 + index], dataList[2 + index], fctmp[0][0], fctmp[0][1],
                         dataList[4 + index], dataList[3 + index], typeStr, dataList[5], dataList[6], dataList[7],
                         dataList[13], dataList[14], dataList[15], dataList[5] - dataList[13],
                         dataList[6] - dataList[14], dataList[7] - dataList[15], "1", "2", "0", self.bookdate, "0")
                sqlList.append(sql)
        mysql.executeAll(sqlList)

    def updateFinalForType239(self, tb1Name, tb2Name, typeStr, typeStr2):

        # --结账单据与SC接口差异
        # sql = "insert into SW_AU_ITVM_ONLINE_AMOUNT_HIERARCHY_DIFFERENCE(RELATION_SOURCE_ID,LINE_CODE,STATION_CODE," \
        #       "SETTLE_TIME,TVM_ORDER,TYPE,DEPOSIT_AMOUNT,SALE_AMOUNT,ERROR_REFUND_AMOUNT,COMPARE_TYPE,BE_DEPOSIT_AMOUNT," \
        #       "BE_SALE_AMOUNT,BE_ERROR_REFUND_AMOUNT,ASSIST_STATUS,REPLACE_STATUS,DATA_TYPE,CREATOR_ID,CREATE_TIME) " \
        #       "select a.id,a.LINE_CODE ,a.STATION_CODE,date_format('%s','%%Y-%%m-%%d'),a.TVM_CODE,%d," \
        #       "b.RECHARGE_MONEY * 100 ,b.SELL_TICKET_MONEY * 100,b.FAULT_REFUND_AMOUNT * 100,%d,sc.recharge_txnamtall,sc.sale_txnamtall,sc.refund_txnamtall,1,0,1,0,now() from sw_st_tvm_daily_bill a,sw_st_tvm_daily_bill_electronic_pay b," \
        #       "(select lineid,station_number,station_equipment_number,sum(sale_txnamtall) sale_txnamtall,sum(refund_txnamtall) refund_txnamtall,sum(recharge_txnamtall) recharge_txnamtall " \
        #       "from %s_fare_statistics where equipmenttype = '1' and paymethod in ('12','13','14','15') and bookdate = '%s' " \
        #       "GROUP BY lineid,station_number,station_equipment_number) sc where a.LINE_CODE = sc.lineid and a.STATION_CODE = sc.station_number and a.TVM_CODE = sc.station_equipment_number " \
        #       "and (date_add(date_format('%s','%%Y-%%m-%%d'), interval 12 hour) " \
        #       "BETWEEN b.START_TIME and  b.END_TIME) and a.APPROVAL_STATUS ='1' and a.DEL_FLAG= '0' and a.ID = b.RELATION_ID " \
        #       "and (ifnull(b.RECHARGE_MONEY,0)*100<>sc.recharge_txnamtall or ifnull(b.SELL_TICKET_MONEY,0)*100<>sc.sale_txnamtall or ifnull(b.FAULT_REFUND_AMOUNT,0)*100<>sc.refund_txnamtall);" \
        #       % (self.bookdate, typeid1, typeid2, typeStr, self.bookdate, self.bookdate)
        # mysql.executeAll([sql])

        sqlList = []
        global mysql
        gol.set_value('dbName', "subway")

        sql = "SELECT DISTINCT * from" \
              " (SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall) AS sc_rt,SUM(sale_txnamtall) AS sc_st,SUM(supplement_txnamtall) AS sc_sut" \
              " FROM %s" \
              " WHERE equipmenttype = 1 AND (paymethod = 12 OR paymethod = 13 OR paymethod = 14 OR paymethod = 15) AND bookdate = %s" \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid) AS t1" \
              " RIGHT  JOIN (SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall) AS lc_rt,SUM(sale_txnamtall) AS lc_st,SUM(supplement_txnamtall) AS lc_sut" \
              " FROM %s" \
              " WHERE equipmenttype = 1 AND (paymethod = 12 OR paymethod = 13 OR paymethod = 14 OR paymethod = 15) AND bookdate = %s" \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid) AS t2 " \
              " on t1.bookdate=t2.bookdate AND t1.lineid=t2.lineid AND t1.station_number = t2.station_number" \
              " AND t1.station_equipment_number = t2.station_equipment_number AND  t1.posoperid = t2.posoperid" \
              " UNION  " \
              " SELECT * from" \
              " (SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall) AS sc_rt,SUM(sale_txnamtall) AS sc_st,SUM(supplement_txnamtall) AS sc_sut" \
              " FROM %s " \
              " WHERE equipmenttype = 1 AND (paymethod = 12 OR paymethod = 13 OR paymethod = 14 OR paymethod = 15) AND bookdate = %s" \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid) AS t1" \
              " LEFT JOIN (SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall) AS lc_rt,SUM(sale_txnamtall) AS lc_st,SUM(supplement_txnamtall) AS lc_sut" \
              " FROM %s" \
              " WHERE equipmenttype = 1 AND (paymethod = 12 OR paymethod = 13 OR paymethod = 14 OR paymethod = 15) AND bookdate = %s " \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid) AS t2 " \
              " on t1.bookdate=t2.bookdate AND t1.lineid=t2.lineid AND t1.station_number = t2.station_number " \
              " AND t1.station_equipment_number = t2.station_equipment_number AND  t1.posoperid = t2.posoperid" % (
                  tb1Name, self.bookdate, tb2Name, self.bookdate, tb1Name, self.bookdate, tb2Name, self.bookdate)
        fc = mysql.select(sql)
        if fc is None:
            return
        for data in fc:
            if data[14] != data[6] or data[13] != data[5] or data[15] != data[7]:

                dataList = list(data)
                index = 0
                if data[0] is None:
                    index = 8
                    dataList[5] = 0
                    dataList[6] = 0
                    dataList[7] = 0
                if data[8] is None:
                    index = 0
                    dataList[13] = 0
                    dataList[14] = 0
                    dataList[15] = 0

                sql = "INSERT INTO  SW_AU_ITVM_ONLINE_AMOUNT_HIERARCHY_DIFFERENCE " \
                      "(RELATION_SOURCE_ID,LINE_CODE,STATION_CODE," \
                      "SETTLE_TIME,TVM_ORDER,TYPE," \
                      "DEPOSIT_AMOUNT,SALE_AMOUNT,ERROR_REFUND_AMOUNT,COMPARE_TYPE,BE_DEPOSIT_AMOUNT,BE_SALE_AMOUNT,BE_ERROR_REFUND_AMOUNT" \
                      ",ASSIST_STATUS,REPLACE_STATUS,DATA_TYPE,CREATOR_ID,CREATE_TIME)" \
                      " VALUES" \
                      " (%d,%s,%s,'%s','%s','%s',%d,%d,%d,%s,%d,%d,%d,%s,%s,%s,%s,now())" \
                      % (0, dataList[1 + index], dataList[2 + index], dataList[0 + index],
                         dataList[3 + index], typeStr,
                         dataList[5], dataList[6], dataList[7], typeStr2, dataList[13], dataList[14], dataList[15]
                         , "1", "2", "2", "0")
                sqlList.append(sql)
        mysql.executeAll(sqlList)

    def accTXTtoSql(self, path):
        gol.set_value('dbName', "subway")
        for root, ds, fs in os.walk(path):
            if root == path:
                for f in fs:
                    if f.find(self.bookdate + ".dat") >= 0:
                        try:
                            fileRead = open(root + "/" + f, encoding='utf-8')
                            while True:
                                content = fileRead.readline()

                                if content == '' or content == "\n":
                                    break
                                listV = content.replace('\n', '').split(",")
                                tmpStr = ""
                                for v in listV:
                                    tmpStr = tmpStr + "\'" + v + "\'" + ","
                                sql = "INSERT INTO subway.%s VALUES (\'%s\',%s)" % (f.split("_")[0], f, tmpStr[:-1])
                                mysql.execute(sql)

                            fileRead.close()
                            mysql.commit()
                        except Exception as e:
                            self.log.error("erro" + f + str(e))

    def txt_to_sql(self, listV, f):
        tmpStr = ""
        for v in listV:
            tmpStr = tmpStr + "\'" + v + "\'" + ","
        sql = "INSERT INTO subway.%s VALUES (\'%s\',%s)" % (f.split("_")[0], f, tmpStr[:-1])
        mysql.execute(sql)

    def alter_linecode(self, oldlineID, stationID):
        stationList14 = [41, 43, 45, 47, 49, 51, 53, 55]
        # eric 20211102 add station = 33
        # stationList54 = [21, 23, 25, 27, 29, 31, 35, 37, 39]
        stationList54 = [21, 23, 25, 27, 29, 31, 33, 35, 37, 39]
        if oldlineID == 14 and stationID in stationList54:
            return 54
        else:
            return oldlineID

    def accFaretoSql(self, path):
        gol.set_value('dbName', "subway")
        for root, ds, fs in os.walk(path):
            if root == path:
                for f in fs:
                    if f.find(self.bookdate + ".dat") >= 0:

                        if f.find("ONETPASS") >= 0:
                            ## Eric 20211110 append 5,6,35,36
                            # deviceType = [1, 2, 3, 4, 7, 31]
                            deviceType = [1, 2, 3, 4, 5, 6, 7, 31, 35, 36, 51]
                            fileRead = open(root + "/" + f, encoding='utf-8')
                            while True:
                                try:
                                    content = fileRead.readline()

                                    if content == '':
                                        break
                                    content = content.rstrip('\n')
                                    listV = content.split(",")

                                    ## Eric 20211115
                                    listV.append(self.bookdate)

                                    self.txt_to_sql(listV, f)
                                    ## Eric 20211116 追加日期，多一个数据元素
                                    """
                                    if listV is None or len(listV) != 11 or int(listV[0], 16) not in deviceType:
                                        continue
                                    """
                                    if listV is None or len(listV) != 12 or int(listV[0], 16) not in deviceType:
                                        continue

                                    mType = int(listV[3][0:2], 16)
                                    subType = int(listV[3][2:4], 16)
                                    sql = ""

                                    dictc[str(mType) + "==" + str(subType)] = "1"
                                    deviceId = listV[1][6:8]
                                    lineID = int(listV[1][2:4])
                                    stationID = int(listV[1][4:6])
                                    # 将机场城际的车站线路修改为 54
                                    # print(lineID+"--线路1"+stationID+"--车站1")
                                    lineID = self.alter_linecode(lineID, stationID)
                                    # print(lineID+"--线路2")

                                    # if listV[0] == "07" and lineID == 6:
                                    #    listV[0] = "02"

                                    if listV[0] == "01":
                                        if lineID == 1 or lineID == 4 or lineID == 5  or (lineID == 10 and stationID != 53) or lineID == 16:
                                            deviceId = "V%03d" % (int(listV[1][6:8], 16) - 60)
                                        else:
                                            deviceId = "V%03d" % (int(listV[1][6:8], 16))
                                    if listV[0] == "02" or listV[0] == "33":
                                        if lineID == 1 or lineID == 4:
                                            deviceId = "BOM%03d" % (int(listV[1][6:8], 16) - 90)
                                        elif lineID == 5 or (lineID == 10 and stationID != 53) or lineID == 16:
                                            deviceId = "BOM%03d" % (int(listV[1][6:8], 16) - 190)
                                        else:
                                            deviceId = "BOM%03d" % (int(listV[1][6:8], 16))
                                    if listV[0] == "03":
                                        deviceId = "STM%03d" % (int(listV[1][6:8], 16))
                                    if listV[0] == "04":
                                        deviceId = "SSM%03d" % (int(listV[1][6:8], 16))
                                    if listV[0] == "07":
                                        deviceId = "BOM%03d" % (int(listV[1][6:8], 16))
                                    ##  deviceId = "ZHM%03d" % (int(listV[1][6:8], 16))
                                    ## Eric 20211116 bookdate 改用 上传日期 即 listV[10] 改为 listV[11]
                                    """ ## begin
                                    if (mType == 8 and subType == 15) or (mType == 3 and subType == 3) or (
                                            mType == 3 and subType == 2) \
                                            or (mType == 3 and subType == 1) or (mType == 3 and subType == 121):
                                        sql = "INSERT INTO subway.acc_fare_statistics (bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[10], lineID, stationID, listV[9], listV[0],
                                                  deviceId, listV[2], listV[5], listV[6], decimal.Decimal(listV[7]),
                                                  decimal.Decimal(listV[8]), 0, 0, 0, 0, 0, 0, 0, 0)

                                    if (mType == 8 and subType == 14) or (mType == 3 and subType == 12) or (
                                            mType == 3 and subType == 11) \
                                            or (mType == 3 and subType == 10):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[10], lineID, stationID, listV[9], listV[0],
                                                  deviceId, listV[2], listV[5], listV[6], 0, 0, 0, 0, 0, 0,
                                                  decimal.Decimal(listV[7]), decimal.Decimal(listV[8]), 0, 0)

                                    if (mType == 33 and subType == 17) or (mType == 34 and subType == 17) or (
                                            mType == 35 and subType == 17) \
                                            or (mType == 3 and subType == 120) or (mType == 3 and subType == 119) or (
                                            mType == 3 and subType == 118):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[10], lineID, stationID, listV[9], listV[0],
                                                  deviceId, listV[2], listV[5], listV[6], 0, 0,
                                                  decimal.Decimal(listV[7]), decimal.Decimal(listV[8]), 0, 0, 0, 0, 0, 0)

                                    if (mType == 3 and subType == 28) or (mType == 3 and subType == 27) or (
                                            mType == 3 and subType == 26) \
                                            or (mType == 80 and subType == 1) or (mType == 81 and subType == 1) \
                                            or (mType == 1 and subType == 80) or (mType == 1 and subType == 81):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[10], lineID, stationID, listV[9], listV[0],
                                                  deviceId, listV[2], listV[5], listV[6], 0, 0, 0, 0,
                                                  decimal.Decimal(listV[7]), decimal.Decimal(listV[8]), 0, 0, 0, 0)


                                    if (mType == 1 and subType == 32) or (mType == 1 and subType == 33) or (mType == 3 and subType == 16 ) or (mType == 3 and subType == 17 ) or (mType == 3 and subType == 18 ) or (mType == 3 and subType == 79 ) or (mType == 3 and subType == 80 ) or (mType == 3 and subType == 81 ):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[10], lineID, stationID, listV[9], listV[0],
                                                  deviceId, listV[2], listV[5], listV[6], 0, 0, 0, 0, 0, 0, 0, 0,
                                                  decimal.Decimal(listV[7]), decimal.Decimal(listV[8]))
                                    """

                                    if (mType == 8 and subType == 15) or (mType == 3 and subType == 3) or (
                                            mType == 3 and subType == 2) \
                                            or (mType == 3 and subType == 1) or (mType == 3 and subType == 121):
                                        sql = "INSERT INTO subway.acc_fare_statistics (bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[11], listV[10], lineID, stationID, listV[9], listV[0],
                                                  deviceId, listV[2], listV[5], listV[6], decimal.Decimal(listV[7]),
                                                  decimal.Decimal(listV[8]), 0, 0, 0, 0, 0, 0, 0, 0)

                                    if (mType == 8 and subType == 14) or (mType == 3 and subType == 12) or (
                                            mType == 3 and subType == 11) \
                                            or (mType == 3 and subType == 10):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[11], listV[10], lineID, stationID, listV[9], listV[0],
                                                  deviceId, listV[2], listV[5], listV[6], 0, 0, 0, 0, 0, 0,
                                                  decimal.Decimal(listV[7]), decimal.Decimal(listV[8]), 0, 0)

                                    if (mType == 33 and subType == 17) or (mType == 34 and subType == 17) or (
                                            mType == 35 and subType == 17) \
                                            or (mType == 3 and subType == 120) or (mType == 3 and subType == 119) or (
                                            mType == 3 and subType == 118):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[11], listV[10], lineID, stationID, listV[9], listV[0],
                                                  deviceId, listV[2], listV[5], listV[6], 0, 0,
                                                  decimal.Decimal(listV[7]), decimal.Decimal(listV[8]), 0, 0, 0, 0, 0,
                                                  0)

                                    if (mType == 3 and subType == 28) or (mType == 3 and subType == 27) or (
                                            mType == 3 and subType == 26) \
                                            or (mType == 80 and subType == 1) or (mType == 81 and subType == 1) \
                                            or (mType == 1 and subType == 80) or (mType == 1 and subType == 81):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[11], listV[10], lineID, stationID, listV[9], listV[0],
                                                  deviceId, listV[2], listV[5], listV[6], 0, 0, 0, 0,
                                                  decimal.Decimal(listV[7]), decimal.Decimal(listV[8]), 0, 0, 0, 0)

                                    if (mType == 1 and subType == 32) or (mType == 1 and subType == 33) or (
                                            mType == 3 and subType == 16) or (mType == 3 and subType == 17) or (
                                            mType == 3 and subType == 18) or (mType == 3 and subType == 79) or (
                                            mType == 3 and subType == 80) or (mType == 3 and subType == 81):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[11], listV[10], lineID, stationID, listV[9], listV[0],
                                                  deviceId, listV[2], listV[5], listV[6], 0 - decimal.Decimal(listV[7]),
                                                  0 - decimal.Decimal(listV[8]), 0, 0, 0, 0, 0, 0,
                                                  decimal.Decimal(listV[7]), decimal.Decimal(listV[8]))

                                    if sql != "":
                                        mysql.execute(sql)
                                    ## Eric 20211115
                                    """
                                    sql = "INSERT INTO subway.SW_CK_ACC_LINE_M_TRANS_STS (LINE_CODE,TransType,productType," \
                                          "productSubType,PamentMethod,transactionNumber,transactionValue,dataDate,equipmenttype) VALUES (" \
                                          "ifnull((SELECT VLINE_CODE FROM subway.sw_param_station WHERE CODE = %d AND LINE_CODE = %d ),%d), '%s', '%s', '%s', '%s', %f, %f, '%s','%s')" \
                                          % (stationID, lineID, lineID, listV[3], listV[5], listV[6], listV[9],
                                             decimal.Decimal(listV[7]).quantize(decimal.Decimal('0.0000000')), decimal.Decimal(listV[8]).quantize(decimal.Decimal('0.0000000')), listV[10], int(listV[0], 16))
                                    """
                                    sql = "INSERT INTO subway.SW_CK_ACC_LINE_M_TRANS_STS (LINE_CODE,TransType,productType," \
                                          "productSubType,PamentMethod,transactionNumber,transactionValue,tradeDate,dataDate,equipmenttype) VALUES (" \
                                          "ifnull((SELECT VLINE_CODE FROM subway.sw_param_station WHERE CODE = %d AND LINE_CODE = %d ),%d), '%s', '%s', '%s', '%s', %f, %f, '%s','%s','%s')" \
                                          % (stationID, lineID, lineID, listV[3], listV[5], listV[6], listV[9],
                                             decimal.Decimal(listV[7]).quantize(decimal.Decimal('0.0000000')),
                                             decimal.Decimal(listV[8]).quantize(decimal.Decimal('0.0000000')),
                                             listV[10], listV[11], int(listV[0], 16))

                                    mysql.execute(sql)

                                except Exception as err:
                                    self.log.error(str(f) + str(err))
                            #
                            fileRead.close()

                        elif f.find("ONECPASS") >= 0:
                            ## Eric 20211110 append 5,6,35,36
                            # deviceType = [1, 2, 3, 4, 7, 31]
                            deviceType = [1, 2, 3, 4, 5, 6, 7, 31, 35, 36, 51]
                            fileRead = open(root + "/" + f, encoding='utf-8')
                            while True:
                                try:
                                    content = fileRead.readline()

                                    if content == '':
                                        break
                                    content = content.rstrip('\n')
                                    listV = content.split(",")
                                    ## Eric 20211115
                                    listV.append(self.bookdate)

                                    self.txt_to_sql(listV, f)
                                    ## Eric 20211116 追加日期，多一个数据元素
                                    """
                                    if listV is None or len(listV) != 10 or int(listV[0][0:2], 16) not in deviceType:
                                        continue
                                    """
                                    if listV is None or len(listV) != 11 or int(listV[0][0:2], 16) not in deviceType:
                                        continue
                                    mType = int(listV[2][0:2], 16)
                                    subType = int(listV[2][2:4], 16)
                                    dictc[str(mType) + "==" + str(subType)] = "1"
                                    sql = ""
                                    deviceId = listV[0][6:8]
                                    lineID = int(listV[0][2:4])
                                    stationID = int(listV[0][4:6])
                                    lineID = self.alter_linecode(lineID, stationID)

                                    # if listV[0][0:2] == "07" and lineID == 6:
                                    #    listV[0][0:2] = "02"

                                    if listV[0][0:2] == "01":
                                        if lineID == 1 or lineID == 4 or lineID == 5 or (lineID == 10 and stationID != 53) or lineID == 16:
                                            deviceId = "V%03d" % (int(listV[0][6:8], 16) - 60)
                                        else:
                                            deviceId = "V%03d" % (int(listV[0][6:8], 16))
                                    elif listV[0][0:2] == "02":
                                        if lineID == 1 or lineID == 4:
                                            deviceId = "BOM%03d" % (int(listV[0][6:8], 16) - 90)
                                        elif lineID == 5 or (lineID == 10 and stationID != 53) or lineID == 16:
                                            deviceId = "BOM%03d" % (int(listV[0][6:8], 16) - 190)
                                        else:
                                            deviceId = "BOM%03d" % (int(listV[0][6:8], 16))
                                    if listV[0][0:2] == "03":
                                        deviceId = "STM%03d" % (int(listV[0][6:8], 16))
                                    if listV[0][0:2] == "04":
                                        deviceId = "SSM%03d" % (int(listV[0][6:8], 16))
                                    if listV[0][0:2] == "07":
                                        deviceId = "BOM%03d" % (int(listV[0][6:8], 16))
                                    ##  deviceId = "ZHM%03d" % (int(listV[0][6:8], 16))
                                    ## Eric 20211116 bookdate 改用 上传日期 即 listV[9] 改为 listV[10]
                                    """ ## begin
                                    if (mType == 8 and subType == 15) or (mType == 3 and subType == 3) or (
                                            mType == 3 and subType == 2) \
                                            or (mType == 3 and subType == 1) or (mType == 3 and subType == 121):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[9], lineID, stationID, listV[8], listV[0][0:2],
                                                  deviceId, listV[1], listV[4], listV[5], decimal.Decimal(listV[6]),
                                                  decimal.Decimal(listV[7]), 0, 0, 0, 0, 0, 0, 0, 0)

                                    if (mType == 8 and subType == 14) or (mType == 3 and subType == 12) or (
                                            mType == 3 and subType == 11) \
                                            or (mType == 3 and subType == 10):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[9], lineID, stationID, listV[8], listV[0][0:2],
                                                  deviceId, listV[1], listV[4], listV[5], 0, 0, 0, 0, 0, 0,
                                                  decimal.Decimal(listV[6]), decimal.Decimal(listV[7]), 0, 0)

                                    if (mType == 33 and subType == 17) or (mType == 34 and subType == 17) or (
                                            mType == 35 and subType == 17) \
                                            or (mType == 3 and subType == 120) or (mType == 3 and subType == 119) or (
                                            mType == 3 and subType == 118):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[9], lineID, stationID, listV[8], listV[0][0:2],
                                                  deviceId, listV[1], listV[4], listV[5], 0, 0,
                                                  decimal.Decimal(listV[6]), decimal.Decimal(listV[7]), 0, 0, 0, 0, 0, 0)

                                    if (mType == 3 and subType == 28) or (mType == 3 and subType == 27) or (
                                            mType == 3 and subType == 26) \
                                            or (mType == 80 and subType == 1) or (mType == 81 and subType == 1) \
                                            or (mType == 1 and subType == 80) or (mType == 1 and subType == 81):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[9], lineID, stationID, listV[8], listV[0][0:2],
                                                  deviceId, listV[1], listV[4], listV[5], 0, 0, 0, 0,
                                                  decimal.Decimal(listV[6]), decimal.Decimal(listV[7]), 0, 0, 0, 0)

                                    if (mType == 1 and subType == 32) or (mType == 1 and subType == 33) or (mType == 3 and subType == 16 ) or (mType == 3 and subType == 17 ) or (mType == 3 and subType == 18 ) or (mType == 3 and subType == 79 ) or (mType == 3 and subType == 80 ) or (mType == 3 and subType == 81 ):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[9], lineID, stationID, listV[8], listV[0][0:2],
                                                  deviceId, listV[1], listV[4], listV[5], 0, 0, 0, 0, 0, 0, 0, 0,
                                                  decimal.Decimal(listV[6]), decimal.Decimal(listV[7]))
                                    ## end
                                    """
                                    if (mType == 8 and subType == 15) or (mType == 3 and subType == 3) or (
                                            mType == 3 and subType == 2) \
                                            or (mType == 3 and subType == 1) or (mType == 3 and subType == 121):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[10], listV[9], lineID, stationID, listV[8], listV[0][0:2],
                                                  deviceId, listV[1], listV[4], listV[5], decimal.Decimal(listV[6]),
                                                  decimal.Decimal(listV[7]), 0, 0, 0, 0, 0, 0, 0, 0)

                                    if (mType == 8 and subType == 14) or (mType == 3 and subType == 12) or (
                                            mType == 3 and subType == 11) \
                                            or (mType == 3 and subType == 10):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[10], listV[9], lineID, stationID, listV[8], listV[0][0:2],
                                                  deviceId, listV[1], listV[4], listV[5], 0, 0, 0, 0, 0, 0,
                                                  decimal.Decimal(listV[6]), decimal.Decimal(listV[7]), 0, 0)

                                    if (mType == 33 and subType == 17) or (mType == 34 and subType == 17) or (
                                            mType == 35 and subType == 17) \
                                            or (mType == 3 and subType == 120) or (mType == 3 and subType == 119) or (
                                            mType == 3 and subType == 118):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[10], listV[9], lineID, stationID, listV[8], listV[0][0:2],
                                                  deviceId, listV[1], listV[4], listV[5], 0, 0,
                                                  decimal.Decimal(listV[6]), decimal.Decimal(listV[7]), 0, 0, 0, 0, 0,
                                                  0)

                                    if (mType == 3 and subType == 28) or (mType == 3 and subType == 27) or (
                                            mType == 3 and subType == 26) \
                                            or (mType == 80 and subType == 1) or (mType == 81 and subType == 1) \
                                            or (mType == 1 and subType == 80) or (mType == 1 and subType == 81):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[10], listV[9], lineID, stationID, listV[8], listV[0][0:2],
                                                  deviceId, listV[1], listV[4], listV[5], 0, 0, 0, 0,
                                                  decimal.Decimal(listV[6]), decimal.Decimal(listV[7]), 0, 0, 0, 0)

                                    if (mType == 1 and subType == 32) or (mType == 1 and subType == 33) or (
                                            mType == 3 and subType == 16) or (mType == 3 and subType == 17) or (
                                            mType == 3 and subType == 18) or (mType == 3 and subType == 79) or (
                                            mType == 3 and subType == 80) or (mType == 3 and subType == 81):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[10], listV[9], lineID, stationID, listV[8], listV[0][0:2],
                                                  deviceId, listV[1], listV[4], listV[5], 0 - decimal.Decimal(listV[6]),
                                                  0 - decimal.Decimal(listV[7]), 0, 0, 0, 0, 0, 0,
                                                  decimal.Decimal(listV[6]), decimal.Decimal(listV[7]))

                                    if sql != "":
                                        mysql.execute(sql)

                                    ## Eric 20211115
                                    """
                                    sql = "INSERT INTO subway.SW_CK_ACC_LINE_T_TRANS_STS (LINE_CODE,TransType,productType," \
                                          "productSubType,PamentMethod,transactionNumber,transactionValue,dataDate,equipmenttype) VALUES (" \
                                          "ifnull((SELECT VLINE_CODE FROM subway.sw_param_station WHERE CODE = %d AND LINE_CODE = %d ),%d), '%s', '%s', '%s', '%s', %f, %f, '%s','%s')" \
                                          % (stationID, lineID, lineID, listV[2], listV[4], listV[5], listV[8],
                                             decimal.Decimal(listV[6]).quantize(decimal.Decimal('0.0000000')), decimal.Decimal(listV[7]).quantize(decimal.Decimal('0.0000000')), listV[9],int(listV[0][0:2], 16))
                                    """
                                    sql = "INSERT INTO subway.SW_CK_ACC_LINE_T_TRANS_STS (LINE_CODE,TransType,productType," \
                                          "productSubType,PamentMethod,transactionNumber,transactionValue,tradeDate,dataDate,equipmenttype) VALUES (" \
                                          "ifnull((SELECT VLINE_CODE FROM subway.sw_param_station WHERE CODE = %d AND LINE_CODE = %d ),%d), '%s', '%s', '%s', '%s', %f, %f, '%s','%s','%s')" \
                                          % (stationID, lineID, lineID, listV[2], listV[4], listV[5], listV[8],
                                             decimal.Decimal(listV[6]).quantize(decimal.Decimal('0.0000000')),
                                             decimal.Decimal(listV[7]).quantize(decimal.Decimal('0.0000000')), listV[9],
                                             listV[10], int(listV[0][0:2], 16))
                                    mysql.execute(sql)

                                except Exception as err:

                                    self.log.error(str(f) + str(err))
                            #
                            fileRead.close()

                        elif f.find("OFFSET") >= 0:
                            fileRead = open(root + "/" + f, encoding='utf-8')
                            while True:
                                try:
                                    content = fileRead.readline()

                                    if content == '':
                                        break
                                    content = content.rstrip('\n')
                                    listV = content.split(",")

                                    self.txt_to_sql(listV, f)

                                    if listV is None or len(listV) != 7:
                                        continue
                                    lineID = int(listV[1])
                                    stID = int(listV[2])
                                    transactionStatus = listV[3]
                                    AMOUNT = decimal.Decimal(listV[4])
                                    times = decimal.Decimal(listV[5])
                                    bookdate = listV[6]

                                    sql = "INSERT INTO subway.SW_CK_ACC_LINE_OFFSET (LINE_CODE,transactionStatus,AMOUNT," \
                                          "TIMES,dataDate) VALUES (" \
                                          "%d, '%s', %d, %d, '%s')" \
                                          % (lineID, transactionStatus, AMOUNT, times, bookdate)

                                    mysql.execute(sql)

                                except Exception as err:

                                    self.log.error(str(f) + str(err))
                            #
                            fileRead.close()

                        elif f.find("TCONSCLEAN") >= 0:
                            fileRead = open(root + "/" + f, encoding='utf-8')
                            while True:
                                try:
                                    content = fileRead.readline()

                                    if content == '':
                                        break
                                    content = content.rstrip('\n')
                                    listV = content.split(",")

                                    self.txt_to_sql(listV, f)

                                    if listV is None or len(listV) != 8:
                                        continue
                                    productIssuerId = listV[0]
                                    CLEANTYPE = int(listV[1])
                                    SERVICEID = int(listV[2])
                                    LINECODE = int(listV[3])
                                    CleanDate = listV[4]
                                    CleanNumber = decimal.Decimal(listV[5]).quantize(decimal.Decimal('0.0000000'))
                                    CleanAmount = decimal.Decimal(listV[6]).quantize(decimal.Decimal('0.0000000'))
                                    bookdate = listV[7]

                                    # if 41 <= SERVICEID <= 48:
                                    #     SERVICEID = 1

                                    sql = "INSERT INTO subway.SW_CK_SYS_CLEANING_CONS_TYPE (SERVICE_ORG,CLEAN_TYPE,TRADE_DATE,LINE_CODE," \
                                          "CLEAN_NUMBER,MONEY,productIssuerId) VALUES (" \
                                          "'%d', '%d','%s',ifnull((SELECT CODE FROM subway.sw_param_line WHERE OPERATOR_ID = %d ),%d),%f,%f,%s)" \
                                          % (SERVICEID, CLEANTYPE, bookdate, SERVICEID, SERVICEID, CleanNumber,
                                             CleanAmount,
                                             productIssuerId)

                                    mysql.execute(sql)

                                except Exception as err:

                                    self.log.error(str(f) + str(err))
                            #
                            fileRead.close()

                        elif f.find("PCONSCLEAN") >= 0:
                            fileRead = open(root + "/" + f, encoding='utf-8')
                            while True:
                                try:
                                    content = fileRead.readline()

                                    if content == '':
                                        break
                                    content = content.rstrip('\n')
                                    listV = content.split(",")

                                    self.txt_to_sql(listV, f)

                                    if listV is None or len(listV) != 9:
                                        continue
                                    productIssuerId = int(listV[0])
                                    SERVICEID = int(listV[1])
                                    LINE_CODE = int(listV[2])
                                    productType = listV[3][0:2]
                                    productSubType = listV[3][2:4]
                                    CleanDate = listV[4]

                                    CleanNumber = decimal.Decimal(listV[5]).quantize(decimal.Decimal('0.0000000'))
                                    CleanAmount = decimal.Decimal(listV[6]).quantize(decimal.Decimal('0.0000000'))
                                    TRADTYPE = listV[7]
                                    bookdate = listV[8]

                                    # if 41 <= SERVICEID <= 48:
                                    #     SERVICEID = 1

                                    sql = "INSERT INTO subway.SW_CK_SYS_CLEANING_CONS_TICKET (productIssuerId,SERVICE_ORG,CLEAN_TYPE,TRADE_DATE,LINE_CODE," \
                                          "CLEAN_NUMBER,MONEY,productType,productSubType) VALUES (" \
                                          "'%d','%d', '%s','%s',ifnull((SELECT CODE FROM subway.sw_param_line WHERE OPERATOR_ID = %d ),%d), %f, %f,'%s',%s)" \
                                          % (productIssuerId, SERVICEID, TRADTYPE, bookdate, SERVICEID, SERVICEID,
                                             CleanNumber, CleanAmount, productType,
                                             productSubType)

                                    mysql.execute(sql)

                                except Exception as err:

                                    self.log.error(str(f) + str(err))
                            #
                            fileRead.close()
                        elif f.find("SALESCLEAN") >= 0:
                            fileRead = open(root + "/" + f, encoding='utf-8')
                            while True:
                                try:
                                    content = fileRead.readline()

                                    if content == '':
                                        break
                                    content = content.rstrip('\n')
                                    listV = content.split(",")

                                    self.txt_to_sql(listV, f)

                                    if listV is None or len(listV) != 9:
                                        continue

                                    SERVICEID = listV[0]
                                    LINE_CODE = int(listV[1])
                                    productType = listV[2]
                                    CleanDate = listV[3]
                                    bili = float(listV[4])
                                    CleanNumber = decimal.Decimal(listV[5])
                                    CleanAmount = decimal.Decimal(listV[6])
                                    paymentMethod = listV[7]
                                    bookdate = listV[8]

                                    sql = "INSERT INTO subway.SW_CK_SYS_CLEANING_SALE_TICKET (SERVICE_ORG,productType,TRADE_DATE,LINE_CODE," \
                                          "MONEY,paymentMethod) VALUES (" \
                                          "'%s', '%s', '%s',%d, %d,'%s')" \
                                          % (SERVICEID, productType, bookdate, LINE_CODE, CleanAmount, paymentMethod)

                                    mysql.execute(sql)

                                except Exception as err:

                                    self.log.error(str(f) + str(err))
                            #
                            fileRead.close()
                        else:
                            fileRead = open(root + "/" + f, encoding='utf-8')
                            while True:
                                try:
                                    content = fileRead.readline()

                                    if content == '':
                                        break
                                    content = content.rstrip('\n')
                                    listV = content.split(",")

                                    self.txt_to_sql(listV, f)

                                except Exception as err:
                                    self.log.error(str(f) + str(err))
                            #
                            fileRead.close()

                        mysql.commit()

                    tmpDir = self.cf.get("option", "tmpDir")
                    isExists = os.path.exists(tmpDir + self.bookdate)
                    if not isExists:
                        os.makedirs(tmpDir + self.bookdate)
                    shutil.copy(root + f, tmpDir + self.bookdate)
                    os.remove(root + f)
        self.log.info(str(dictc))

    def box5109ToSubway(self):
        global mysql
        gol.set_value('dbName', "subway")

        # 预处理 分钱箱
        # 1456
        # sql = "DELETE FROM transaction_datasc.cash_drawer_replacement " \
        #      "WHERE operation_object != 1 " \
        #      "AND  operation_object != 3 " \
        #      "AND operation_object != 2 " \
        #      "AND operation_object != 5 " \
        #      "AND operation_object != 10"
        # mysql.executeAll([sql])

        # sql = "DELETE FROM transaction_datasc.cash_box_replacement " \
        #      "WHERE (line_number = 2 or line_number = 14 or line_number = 37) " \
        #      "and cash_box_location_id != 1 " \
        #      "AND  cash_box_location_id != 2 " \
        #      "AND cash_box_location_id != 5 " \
        #      "AND cash_box_location_id != 9 " \
        #      "AND cash_box_location_id != 10"
        # mysql.executeAll([sql])

        # sql = "DELETE FROM transaction_datasc.cash_box_replacement " \
        #      "WHERE line_number = 3 " \
        #      "and cash_box_location_id != 1 " \
        #      "AND  cash_box_location_id != 2 " \
        #      "AND cash_box_location_id != 3 " \
        #      "AND cash_box_location_id != 5 " \
        #      "AND cash_box_location_id != 7"
        # mysql.executeAll([sql])

        ##1/4/6/5/16号线
        ##小于次日0点的装入，放到当天
        sql = "insert into subway.sc_tvm_coin_opt_swich (bookdate,lineid,station_number,station_equipment_number,cash_box_type,coin_number,opt_type,amount)" \
              " select '%s',CAST(trim(replace(line_number, '\t', '')) AS SIGNED),CAST(LPAD(station_number,2,0) AS SIGNED)," \
              " concat('V',LPAD(equipment_number- (case line_number when 9 then 0 else 60 end),3,0))," \
              " case when operation_object = 2 then 1 when operation_object in (5,6) then 2 when operation_object = 1 then 3 when operation_object in (3,4) then 4 when 10 then 5 end," \
              "right(cash_box_id,8)," \
              " case operation_type when 1 then 2 when 2 then 1 end,sum(operation_amount) coins_number" \
              " from transaction_datasc.cash_drawer_replacement" \
              " where equipment_type = 1 and operation_type = 2 and operation_object in (1,2,3,5,10) and right(replacement_time,14) <='%s2359' " \
              " group by line_number,station_number,equipment_number,operation_object,cash_box_id,operation_type;" % (
                  self.bookdate, self.bookdate)
        mysql.execute(sql)

        nextDay = (datetime.datetime.strptime(self.bookdate, "%Y%m%d") + datetime.timedelta(days=+1)).strftime("%Y%m%d")
        sql = "insert into subway.sc_tvm_coin_opt_swich (bookdate,lineid,station_number,station_equipment_number,cash_box_type,coin_number,opt_type,amount)" \
              " select '%s',CAST(trim(replace(line_number, '\t', '')) AS SIGNED),CAST(LPAD(station_number,2,0) AS SIGNED)," \
              "concat('V',LPAD(equipment_number-(case line_number when 9 then 0 else 60 end),3,0))," \
              " case when operation_object = 2 then 1 when operation_object in (5,6) then 2 when operation_object = 1 then 3 when operation_object in (3,4) then 4 when 10 then 5 end," \
              "right(cash_box_id,8),1,sum(operation_amount) coins_number" \
              " from transaction_datasc.cash_drawer_replacement" \
              " where equipment_type = 1 and operation_type = 2 and operation_object in (1,2,3,5,10) and right(replacement_time,14) >'%s2359' " \
              " group by line_number,station_number,equipment_number,operation_object,cash_box_id,operation_type;" \
              % (nextDay, self.bookdate)
        mysql.execute(sql)

        sql = "insert into subway.sc_tvm_coin_opt_swich (bookdate,lineid,station_number,station_equipment_number,cash_box_type,coin_number,opt_type,amount)" \
              " select '%s',CAST(trim(replace(line_number, '\t', '')) AS SIGNED),CAST(LPAD(station_number,2,0) AS SIGNED)," \
              "concat('V',LPAD(equipment_number-(case line_number when 9 then 0 else 60 end),3,0))," \
              " case when operation_object = 2 then 1 when operation_object in (5,6) then 2 when operation_object = 1 then 3 when operation_object in (3,4) then 4 when 10 then 5 end," \
              "right(cash_box_id,8)," \
              " case operation_type when 1 then 2 when 2 then 1 end,sum(operation_amount) coins_number" \
              " from transaction_datasc.cash_drawer_replacement " \
              " where equipment_type = 1 and operation_type = 1 and operation_object in (1,2,3,5,10) " \
              " group by line_number,station_number,equipment_number,operation_object,cash_box_id,operation_type;" \
              % (self.bookdate)
        mysql.execute(sql)

        ##2/3/14/西户号线
        ##小于次日0点的装入，放到当天
        sql = "insert into subway.sc_tvm_coin_opt_swich (bookdate,lineid,station_number,station_equipment_number,cash_box_type,coin_number,opt_type,amount)" \
              " select '%s',CAST(trim(replace(line_number, '\t', '')) AS SIGNED),CAST(right(station_number,2) AS SIGNED),concat('V',LPAD(equipment_number,3,0))," \
              " case when (((line_number = 2) and cash_box_location_id = 2) or (line_number = 3 and cash_box_location_id in (1,2)) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (4,5))) then 1 when (((line_number = 2) and cash_box_location_id = 5) or (line_number = 3 and cash_box_location_id = 3) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (2))) then 2 when (((line_number = 2) and cash_box_location_id = 1) or (line_number = 3 and cash_box_location_id = 5) or ((line_number = 14 or line_number = 37 or line_number = 36)  and cash_box_location_id in (1))) then 3 when (((line_number = 2) and cash_box_location_id = 10) or (line_number = 3 and cash_box_location_id = 7) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (7,8))) then 4 when (((line_number = 2) and cash_box_location_id = 9) or (line_number = 3 and cash_box_location_id = 6) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (6))) then 5 end," \
              "case when line_number = 3 then CONCAT(SUBSTRING(right(money_box_id,8),1,4),LPAD(conv(right(money_box_id,4), 16, 10 ), 4, 0)) else  right(money_box_id,8) end ,change_operation_type,sum(coins_number)*100 coins_number" \
              " from transaction_datasc.cash_box_replacement where equipment_type = 1 and change_operation_type = 1 " \
              " and right(business_data_generation_time,14) <='%s2359' " \
              "group by line_number,station_number,equipment_number,cash_box_location_id,money_box_id,change_operation_type;" \
              % (self.bookdate, self.bookdate)
        mysql.execute(sql)

        nextDay = (datetime.datetime.strptime(self.bookdate, "%Y%m%d") + datetime.timedelta(days=+1)).strftime("%Y%m%d")
        sql = "insert into subway.sc_tvm_coin_opt_swich " \
              "(bookdate,lineid,station_number,station_equipment_number,cash_box_type,coin_number,opt_type,amount) " \
              " select '%s',CAST(trim(replace(line_number, '\t', '')) AS SIGNED),CAST(right(station_number,2) AS SIGNED),concat('V',LPAD(equipment_number,3,0))," \
              " case when (((line_number = 2) and cash_box_location_id = 2) or (line_number = 3 and cash_box_location_id in (1,2) ) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (4,5))) then 1 when (((line_number = 2) and cash_box_location_id = 5) or (line_number = 3 and cash_box_location_id = 3) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (2))) then 2 when (((line_number = 2) and cash_box_location_id = 1) or (line_number = 3 and cash_box_location_id = 5) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (1))) then 3 when (((line_number = 2) and cash_box_location_id = 10) or (line_number = 3 and cash_box_location_id = 7) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (7,8))) then 4 when (((line_number = 2) and cash_box_location_id = 9) or (line_number = 3 and cash_box_location_id = 6) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (6))) then 5 end," \
              "case when line_number = 3 then CONCAT(SUBSTRING(right(money_box_id,8),1,4),LPAD(conv(right(money_box_id,4), 16, 10 ), 4, 0)) else  right(money_box_id,8) end ,change_operation_type,sum(coins_number)*100 coins_number " \
              " from transaction_datasc.cash_box_replacement " \
              " where equipment_type = 1 and change_operation_type = 1 and right(business_data_generation_time,14) >'%s2359' " \
              " group by line_number,station_number,equipment_number,cash_box_location_id,money_box_id,change_operation_type;" \
              % (nextDay, self.bookdate)
        mysql.execute(sql)

        sql = "insert into subway.sc_tvm_coin_opt_swich(bookdate,lineid,station_number,station_equipment_number,cash_box_type,coin_number,opt_type,amount) " \
              " select '%s',CAST(trim(replace(line_number, '\t', '')) AS SIGNED),CAST(right(station_number,2) AS SIGNED),concat('V',LPAD(equipment_number,3,0))," \
              " case when (((line_number = 2) and cash_box_location_id = 2) or (line_number = 3 and cash_box_location_id in (1,2) ) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (4,5))) then 1 when (((line_number = 2) and cash_box_location_id = 5) or (line_number = 3 and cash_box_location_id = 3) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (2))) then 2 when (((line_number = 2) and cash_box_location_id = 1) or (line_number = 3 and cash_box_location_id = 5) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (1))) then 3 when (((line_number = 2) and cash_box_location_id = 10) or (line_number = 3 and cash_box_location_id = 7) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (7,8))) then 4 when (((line_number = 2) and cash_box_location_id = 9) or (line_number = 3 and cash_box_location_id = 6) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (6))) then 5 end," \
              "case when line_number = 3 then CONCAT(SUBSTRING(right(money_box_id,8),1,4),LPAD(conv(right(money_box_id,4), 16, 10 ), 4, 0)) else  right(money_box_id,8) end ,change_operation_type,sum(coins_number)*100 coins_number " \
              " from transaction_datasc.cash_box_replacement" \
              " where equipment_type = 1 and change_operation_type = 2 " \
              " group by line_number,station_number,equipment_number,cash_box_location_id,money_box_id,change_operation_type;" \
              % (self.bookdate)
        mysql.execute(sql)

        mysql.commit()

        sql = "select bookdate,lineid,station_number,station_equipment_number,cash_box_type,opt_type,sum(amount),sum(supplement_amount) from subway.sc_tvm_coin_opt_swich group by bookdate,lineid,station_number,station_equipment_number,cash_box_type,opt_type"
        fc = mysql.select(sql)

        sqlList = []
        if fc is None:
            return

        for data in fc:
            sql = "select bookdate,CAST(trim(replace(lineid, '\t', '')) AS SIGNED),CAST(station_number AS SIGNED),station_equipment_number,cash_box_type , opt_type  from subway.sc_tvm_coin_opt where bookdate = '%s' and lineid = '%s' and station_number = '%s'" \
                  " and station_equipment_number = '%s' and cash_box_type = '%s' and opt_type = %s" \
                  % (data[0], data[1], data[2], data[3], data[4], data[5])
            fc = mysql.select(sql)
            if (fc is None) or len(fc) <= 0:
                sql = "INSERT INTO subway.sc_tvm_coin_opt (bookdate,lineid,station_number,station_equipment_number,cash_box_type ,opt_type,amount,supplement_amount)VALUES ('%s','%s','%s','%s','%s','%s',%d,0)" \
                      % (data[0], data[1], data[2], data[3], data[4], data[5], data[6])
                sqlList.append(sql)
            else:
                sql = "UPDATE subway.sc_tvm_coin_opt " \
                      " SET  amount = amount + %d  " \
                      " WHERE  bookdate = '%s' and lineid = '%s' and station_number = '%s'" \
                      " and station_equipment_number = '%s' and cash_box_type = '%s' and opt_type = %s" \
                      % (data[6], data[0], data[1], data[2], data[3], data[4], data[5])
                sqlList.append(sql)
        mysql.executeAll(sqlList)

        mysql.executeAll(["truncate table subway.sc_tvm_coin_opt_swich"])

    def out_ticket_all(self,sclc):
        global mysql
        sqlList = []
        if sclc == "sc":
            sql = "INSERT ignore INTO subway.sc_out_ticket (tickettype,lineid,stationid,equipmenttype,station_equipment_number,posoperid,transactionNumber,transactionValue,bookdate)" \
                  "SELECT 0,lineid,station_number,case equipmenttype when 7 then 2 when 1 then 1 when 2 then 2 end," \
                  "case  when equipmenttype = 1 AND (lineid = 1 or lineid = 4 or lineid = 5 or lineid = 16) then  concat('V',LPAD(station_equipment_number-60,3,0)) " \
                  "when equipmenttype = 2 AND (lineid = 1 or lineid = 4) then concat('BOM',LPAD(station_equipment_number-90,3,0)) " \
                  "when equipmenttype = 2 AND (lineid = 5 or lineid = 16) then concat('BOM',LPAD(station_equipment_number-190,3,0)) " \
                  "when equipmenttype = 1 AND (lineid = 2 OR lineid = 3 or lineid  = 6 or lineid  = 9 or lineid = 14 or lineid = 37 or lineid = 36) then  concat('V',LPAD(station_equipment_number,3,0))" \
                  "when equipmenttype = 2 AND (lineid = 2 OR lineid = 3 or lineid  = 9 or lineid = 14 or lineid = 37 or lineid = 36) then  concat('BOM',LPAD(station_equipment_number,3,0))" \
                  "when equipmenttype = 7 AND (lineid = 6 or lineid = 9) then  concat('BOM',LPAD(station_equipment_number,3,0))" \
                  "END," \
                  "replace(posoperid,'\t',''),SUM(carnum),SUM(txnamtall),%s FROM  transaction_datasc.big_data_statistics WHERE udtype = 3 AND udsubtype = 121 " \
                  "AND  txnamtall = 0 AND bookdate = %s " \
                  "GROUP BY lineid,station_number,equipmenttype,station_equipment_number,posoperid ;" % (
                      self.bookdate, self.bookdate)
            sqlList.append(sql)
            sql = "INSERT ignore INTO subway.sc_out_ticket (tickettype,lineid,stationid,equipmenttype,station_equipment_number,posoperid,transactionNumber,transactionValue,bookdate)" \
                  "SELECT 1,lineid,station_number,case equipmenttype when 7 then 2 when 1 then 1 when 2 then 2 end," \
                  "case  when equipmenttype = 1 AND (lineid = 1 or lineid = 4 or lineid = 5 or lineid = 16) then  concat('V',LPAD(station_equipment_number-60,3,0)) " \
                  "when equipmenttype = 2 AND (lineid = 1 or lineid = 4 ) then concat('BOM',LPAD(station_equipment_number-90,3,0)) " \
                  "when equipmenttype = 2 AND (lineid = 5 or lineid = 16) then concat('BOM',LPAD(station_equipment_number-190,3,0)) " \
                  "when equipmenttype = 1 AND (lineid = 2 OR lineid = 3 or lineid  = 6 or lineid  = 9 or lineid = 14 or lineid = 37 or lineid = 36) then  concat('V',LPAD(station_equipment_number,3,0))" \
                  "when equipmenttype = 2 AND (lineid = 2 OR lineid = 3 or lineid  = 9 or lineid = 14 or lineid = 37 or lineid = 36) then  concat('BOM',LPAD(station_equipment_number,3,0))" \
                  "when equipmenttype = 7 AND (lineid = 6 or lineid  = 9) then  concat('BOM',LPAD(station_equipment_number,3,0))" \
                  "END," \
                  "replace(posoperid,'\t',''),SUM(carnum),SUM(txnamtall),%s FROM  transaction_datasc.big_data_statistics WHERE udtype = 3 AND udsubtype = 121 " \
                  "AND  txnamtall != 0 AND bookdate = %s " \
                  "GROUP BY lineid,station_number,equipmenttype,station_equipment_number,posoperid ;" % (
                      self.bookdate, self.bookdate)
            sqlList.append(sql)

        elif sclc == "lc":
            sql = "INSERT ignore INTO subway.lc_out_ticket (tickettype,lineid,stationid,equipmenttype,station_equipment_number,posoperid,transactionNumber,transactionValue,bookdate)" \
                  "SELECT 0,lineid,station_number,case equipmenttype when 7 then 2 when 1 then 1 when 2 then 2 end," \
                  "case  when equipmenttype = 1 AND (lineid = 1 or lineid = 4 or lineid = 5 or lineid = 16) then  concat('V',LPAD(station_equipment_number-60,3,0)) " \
                  "when equipmenttype = 2 AND (lineid = 1 or lineid = 4 ) then concat('BOM',LPAD(station_equipment_number-90,3,0)) " \
                  "when equipmenttype = 2 AND (lineid  = 5 or lineid = 16) then concat('BOM',LPAD(station_equipment_number-190,3,0)) " \
                  "when equipmenttype = 1 AND (lineid = 2 OR lineid = 3 or lineid  = 6 or lineid  = 9 or lineid = 14 or lineid = 37 or lineid = 36) then  concat('V',LPAD(station_equipment_number,3,0))" \
                  "when equipmenttype = 2 AND (lineid = 2 OR lineid = 3 or lineid  = 9 or lineid = 14 or lineid = 37 or lineid = 36) then  concat('BOM',LPAD(station_equipment_number,3,0))" \
                  "when equipmenttype = 7 AND (lineid = 6 or lineid  = 9) then  concat('BOM',LPAD(station_equipment_number,3,0))" \
                  "END," \
                  "replace(posoperid,'\t',''),SUM(carnum),SUM(txnamtall),%s FROM  transaction_datalc.big_data_statistics WHERE udtype = 3 AND udsubtype = 121 " \
                  "AND  txnamtall = 0 AND bookdate = %s " \
                  "GROUP BY lineid,station_number,equipmenttype,station_equipment_number,posoperid ;" % (
                      self.bookdate, self.bookdate)
            sqlList.append(sql)
            sql = "INSERT ignore INTO subway.lc_out_ticket (tickettype,lineid,stationid,equipmenttype,station_equipment_number,posoperid,transactionNumber,transactionValue,bookdate)" \
                  "SELECT 1,lineid,station_number,case equipmenttype when 7 then 2 when 1 then 1 when 2 then 2 end," \
                  "case  when equipmenttype = 1 AND (lineid = 1 or lineid = 4 or lineid = 5 or lineid = 16) then  concat('V',LPAD(station_equipment_number-60,3,0)) " \
                  "when equipmenttype = 2 AND (lineid = 1 or lineid = 4 ) then concat('BOM',LPAD(station_equipment_number-90,3,0)) " \
                  "when equipmenttype = 2 AND (lineid = 5 or lineid = 16) then concat('BOM',LPAD(station_equipment_number-190,3,0)) " \
                  "when equipmenttype = 1 AND (lineid = 2 OR lineid = 3 or lineid  = 6 or lineid  = 9 or lineid = 14 or lineid = 37 or lineid = 36) then  concat('V',LPAD(station_equipment_number,3,0))" \
                  "when equipmenttype = 2 AND (lineid = 2 OR lineid = 3 or lineid  = 9 or lineid = 14 or lineid = 37 or lineid = 36) then  concat('BOM',LPAD(station_equipment_number,3,0))" \
                  "when equipmenttype = 7 AND (lineid = 6 or lineid  = 9) then  concat('BOM',LPAD(station_equipment_number,3,0))" \
                  "END," \
                  "replace(posoperid,'\t',''),SUM(carnum),SUM(txnamtall),%s FROM  transaction_datalc.big_data_statistics WHERE udtype = 3 AND udsubtype = 121 " \
                  "AND  txnamtall != 0 AND bookdate = %s " \
                  "GROUP BY lineid,station_number,equipmenttype,station_equipment_number,posoperid ;" % (
                      self.bookdate, self.bookdate)
            sqlList.append(sql)

        mysql.executeAll(sqlList)

    def out_ticket_acc(self):
        global mysql

        sql = "INSERT ignore INTO subway.acc_out_ticket (tickettype,lineid,stationid,equipmenttype,station_equipment_number,posoperid,transactionNumber,transactionValue,bookdate) " \
              "SELECT TYPEt,lineid,stid,TYPE,eqnum,opid,SUM(transactionNumber),SUM(transactionValue),bookdate  FROM  " \
              "(SELECT 0 AS TYPEt, CAST(CONVERT(substr(deviceid,3,2),SIGNED) AS CHAR) AS lineid , CAST(CONVERT(substr(deviceid,5,2), SIGNED) AS CHAR) AS stid, CAST(CONVERT(substr(deviceid,1,2), SIGNED) AS CHAR) AS  type," \
              "case " \
              "when CONVERT(substr(deviceid,1,2), SIGNED) = 1 AND (CONVERT(substr(deviceid,3,2), SIGNED) = 1 OR CONVERT(substr(deviceid,3,2), SIGNED) = 4) then  concat('V',LPAD( CONV(substr(deviceid,7,2), 16, 10)-60,3,0)) " \
              "when CONVERT(substr(deviceid,1,2), SIGNED) = 2 AND (CONVERT(substr(deviceid,3,2), SIGNED) = 1 OR CONVERT(substr(deviceid,3,2), SIGNED) = 4) then concat('BOM',LPAD(CONV(substr(deviceid,7,2), 16, 10)-90,3,0)) " \
              "when CONVERT(substr(deviceid,1,2), SIGNED) = 1  then  concat('V',LPAD(CONVERT(substr(deviceid,7,2), SIGNED),3,0))" \
              "when CONVERT(substr(deviceid,1,2), SIGNED) = 2  then  concat('BOM',LPAD(CONVERT(substr(deviceid,7,2), SIGNED),3,0)) END AS eqnum," \
              "operator_id AS opid,transactionNumber,transactionValue,'%s' AS bookdate " \
              "FROM  subway.onetpass WHERE udtype = '0379' AND  transactionValue = '0' AND dataDate = '%s') AS tb1 " \
              "GROUP BY TYPEt,lineid,stid,bookdate,TYPE,eqnum,opid" % (
                  self.bookdate, self.bookdate)

        mysql.executeAll([sql])

        sql = "INSERT ignore INTO subway.acc_out_ticket (tickettype,lineid,stationid,equipmenttype,station_equipment_number,posoperid,transactionNumber,transactionValue,bookdate) " \
              "SELECT TYPEt,lineid,stid,TYPE,eqnum,opid,SUM(transactionNumber),SUM(transactionValue),bookdate  FROM  " \
              "(SELECT 1 AS TYPEt, CAST(CONVERT(substr(deviceid,3,2),SIGNED) AS CHAR) AS lineid , CAST(CONVERT(substr(deviceid,5,2), SIGNED) AS CHAR) AS stid, CAST(CONVERT(substr(deviceid,1,2), SIGNED) AS CHAR) AS  type," \
              "case " \
              "when CONVERT(substr(deviceid,1,2), SIGNED) = 1 AND (CONVERT(substr(deviceid,3,2), SIGNED) = 1 OR CONVERT(substr(deviceid,3,2), SIGNED) = 4) then  concat('V',LPAD( CONV(substr(deviceid,7,2), 16, 10)-60,3,0)) " \
              "when CONVERT(substr(deviceid,1,2), SIGNED) = 2 AND (CONVERT(substr(deviceid,3,2), SIGNED) = 1 OR CONVERT(substr(deviceid,3,2), SIGNED) = 4) then concat('BOM',LPAD(CONV(substr(deviceid,7,2), 16, 10)-90,3,0)) " \
              "when CONVERT(substr(deviceid,1,2), SIGNED) = 1  then  concat('V',LPAD(CONVERT(substr(deviceid,7,2), SIGNED),3,0))" \
              "when CONVERT(substr(deviceid,1,2), SIGNED) = 2  then  concat('BOM',LPAD(CONVERT(substr(deviceid,7,2), SIGNED),3,0)) END AS eqnum," \
              "operator_id AS opid,transactionNumber,transactionValue,'%s' AS bookdate " \
              "FROM  subway.onetpass WHERE udtype = '0379' AND  transactionValue != '0' AND dataDate = '%s') AS tb1 " \
              "GROUP BY TYPEt,lineid,stid,bookdate,TYPE,eqnum,opid" % (
                  self.bookdate, self.bookdate)

        mysql.executeAll([sql])

    def dayDataSave(self):
        global mysql

        date7 = (datetime.datetime.strptime(self.bookdate, "%Y%m%d") + datetime.timedelta(days=-4)).strftime("%Y%m%d")

        sql = "DELETE FROM subway.sw_ck_system_clean_view WHERE TRADE_DATE BETWEEN %s and %s" % (
            date7, self.bookdate)

        mysql.executeAll([sql])

        sql = "INSERT INTO subway.sw_ck_system_clean_view (TRADE_DATE,LINE_CODE,ONEWAY_MONEY,FACE_MONEY,QR_MONEY,SMART_CARD_MONEY,TRAFFIC_SMART_CARD_MONEY,TOTAL_MONEY) " \
              " SELECT ccc.TRADE_DATE AS TRADE_DATE,ccc.LINE_CODE AS LINE_CODE," \
              "TRUNCATE(ccc.ONEWAY_MONEY,4) AS ONEWAY_MONEY,ccc.FACE_MONEY AS FACE_MONEY,ccc.QR_MONEY AS QR_MONEY,ccc.SMART_CARD_MONEY AS SMART_CARD_MONEY,ccc.TRAFFIC_SMART_CARD_MONEY AS TRAFFIC_SMART_CARD_MONEY," \
              "TRUNCATE(((((ccc.ONEWAY_MONEY + ccc.FACE_MONEY) + ccc.QR_MONEY) + ccc.SMART_CARD_MONEY) + ccc.TRAFFIC_SMART_CARD_MONEY),2) AS TOTAL_MONEY " \
              " FROM ( SELECT a.TRADE_DATE AS TRADE_DATE,a.LINE_CODE AS LINE_CODE,((a.ONEWAY_MONEY_tmp + IFNULL(((" \
              " SELECT SUM(subway.tconsclean.CleanAmount)" \
              " FROM subway.tconsclean " \
              " WHERE ((subway.tconsclean.CLEANTYPE = 57) AND (subway.tconsclean.dataDate = (a.TRADE_DATE + INTERVAL 0 DAY)) AND (subway.tconsclean.SERVICEID = a.LINE_CODE))" \
              " GROUP BY subway.tconsclean.CLEANTYPE,subway.tconsclean.SERVICEID) / 100.00),0)) + IFNULL((( " \
              " SELECT SUM(subway.tconsclean.CleanAmount) " \
              "FROM subway.tconsclean " \
              "WHERE ((subway.tconsclean.CLEANTYPE = 56) AND (subway.tconsclean.dataDate = a.TRADE_DATE) AND (subway.tconsclean.SERVICEID = a.LINE_CODE)) " \
              " GROUP BY subway.tconsclean.CLEANTYPE,subway.tconsclean.SERVICEID) / 100.00),0)) AS ONEWAY_MONEY,a.FACE_MONEY AS FACE_MONEY,a.QR_MONEY AS QR_MONEY,a.SMART_CARD_MONEY AS SMART_CARD_MONEY,a.TRAFFIC_SMART_CARD_MONEY AS TRAFFIC_SMART_CARD_MONEY " \
              " FROM ( " \
              "SELECT subway.pconsclean.SERVICEID AS LINE_CODE,subway.pconsclean.dataDate AS TRADE_DATE, CAST((SUM(if((subway.pconsclean.productIssuerId = 99),subway.pconsclean.CleanAmount,0)) / 100) AS DECIMAL(15,4)) AS SMART_CARD_MONEY, CAST((SUM(if((subway.pconsclean.productIssuerId = 90),subway.pconsclean.CleanAmount,0)) / 100) AS DECIMAL(15,4)) AS TRAFFIC_SMART_CARD_MONEY, CAST((SUM(if(((subway.pconsclean.productIssuerId = 1) AND (subway.pconsclean.productType in ('1611') )),subway.pconsclean.CleanAmount,0)) / 100) AS DECIMAL(15,4)) AS FACE_MONEY, CAST((SUM(if(((subway.pconsclean.productIssuerId = 1) AND ((subway.pconsclean.productType LIKE \'01%%\') OR (subway.pconsclean.productType LIKE \'02%%\') OR (subway.pconsclean.productType LIKE \'03%%\'))),subway.pconsclean.CleanAmount,0)) / 100) AS DECIMAL(15,4)) AS ONEWAY_MONEY_tmp, CAST((SUM(if(((subway.pconsclean.productIssuerId = 1) AND (subway.pconsclean.productType LIKE \'16%%\')  and subway.pconsclean.productType not in('1611')),subway.pconsclean.CleanAmount,0)) / 100) AS DECIMAL(15,4)) AS QR_MONEY " \
              "FROM subway.pconsclean " \
              "WHERE (subway.pconsclean.TRADTYPE = 53 or subway.pconsclean.TRADTYPE = 0) AND subway.pconsclean.datadate BETWEEN %s and %s " \
              "GROUP BY subway.pconsclean.SERVICEID,subway.pconsclean.dataDate " \
              "ORDER BY subway.pconsclean.dataDate DESC, CAST(subway.pconsclean.SERVICEID AS signed)) a) ccc" % (
                  date7, self.bookdate)

        mysql.executeAll([sql])

    def bu_qian_xiang(self):
        global mysql
        sql = "update sw_st_input_cashbox_uninstalled b,sc_tvm_coin_opt a " \
              "SET b.SHOULD_BE_UNLOADED_AMOUNT = a.amount/100," \
              "b.DIFFERENCE_AMOUNT = a.amount/100 - UNINSTALL_AMOUNT " \
              "WHERE DATE_FORMAT(b.UNINSTALL_DATE,'%%Y%%m%%d') = %s AND  a.bookdate = DATE_FORMAT(b.UNINSTALL_DATE,'%%Y%%m%%d')  AND a.lineid = b.LINE_CODE AND a.station_number = b.STATION_CODE " \
              "AND a.station_equipment_number = b.TVM_CODE AND a.cash_box_type = (select value from subway.sys_dict_item where type='stm_cash_box_type' and label= b.MONEY_BOX_TYPE) AND a.opt_type = 2 " % self.bookdate
        mysql.executeAll([sql])

        sql = "update sw_au_tvm_box_amount_difference a,sw_st_input_cashbox_uninstalled b " \
              "SET a.SYSTEM_MONEY = a.SYSTEM_MONEY + b.SHOULD_BE_UNLOADED_AMOUNT," \
              "a.SC_UNLOAD_AMOUNT = a.SC_UNLOAD_AMOUNT + b.SHOULD_BE_UNLOADED_AMOUNT," \
              "DIFF_MONEY = DIFF_MONEY - b.SHOULD_BE_UNLOADED_AMOUNT" \
              " where a.AUDIT_STATUS = 0 AND a.DEL_FLAG = 0 AND b.UNINSTALL_DATE = %s and b.UNINSTALLED_DATE between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME  AND a.LINE_CODE = b.LINE_CODE AND a.STATION_CODE = b.STATION_CODE " \
              "AND a.TVM_CODE = b.TVM_CODE AND a.cash_box_type = b.MONEY_BOX_TYPE" % self.bookdate
        #mysql.executeAll([sql])  

    def exception_logging(self,sclcacc):
        global mysql
        sqlList=[]
        if sclcacc == "sc":
            # SC车站无数据判断和记录日志
            sql = "insert into subway.sw_sclcacc_exception_log (TRADE_DATE,EXCEPTION_TYPE,EXCEPTION_FILE_NAME,LINEID,STATIONID,CREATE_TIME,UPDATE_TIME,ERR_TYPE,ERR_MSG) " \
                  " select '%s','1','sc_fare_statistics'," \
                  " b.line_code,b.code,now(),now(),'2','车站SC无数据'" \
                  " from subway.sw_param_station b left join subway.sc_fare_statistics a " \
                  " on b.line_code = a.lineid and b.code = a.station_number and a.bookdate = '%s' " \
                  "WHERE a.station_number is null and b.IS_ACTIVE = 1;" % (self.bookdate, self.bookdate)
            sqlList.append(sql)

            # SC车站钱箱无数据判断和记录日志
            sql = "insert into subway.sw_sclcacc_exception_log (TRADE_DATE,EXCEPTION_TYPE,EXCEPTION_FILE_NAME,LINEID,STATIONID,CREATE_TIME,UPDATE_TIME,ERR_TYPE,ERR_MSG) " \
                  " select '%s','1','sc_tvm_coin_opt'," \
                  " b.line_code,b.code,now(),now(),'2','车站SC钱箱无数据'" \
                  " from subway.sw_ck_data_submit_log l " \
                  " left join subway.sw_param_station b on l.line_code = b.line_code and l.station_code = b.code and b.IS_ACTIVE = 1" \
                  " left join subway.sc_tvm_coin_opt a " \
                  " on l.line_code = a.lineid and l.station_code = a.station_number and a.bookdate = '%s'" \
                  " WHERE '%s' between l.begin_time and l.end_time and a.station_number is null ;" % (
                  self.bookdate, self.bookdate, self.bookdate)
            sqlList.append(sql)
        elif sclcacc == "lc":
            # LC无数据判断和记录日志
            sql = "insert into subway.sw_sclcacc_exception_log (TRADE_DATE,EXCEPTION_TYPE,EXCEPTION_FILE_NAME,LINEID,STATIONID,CREATE_TIME,UPDATE_TIME,ERR_TYPE,ERR_MSG) " \
                  " select '%s','2','lc_fare_statistics'," \
                  " b.line_code,b.code,now(),now(),'2','车站LC无数据'" \
                  " from subway.sw_param_station b left join subway.lc_fare_statistics a " \
                  " on b.line_code = a.lineid and b.code = a.station_number and a.bookdate = '%s'" \
                  " WHERE a.station_number is null and b.IS_ACTIVE = 1;" % (self.bookdate, self.bookdate)
            sqlList.append(sql)
        elif sclcacc == "aclc":
            # ACC无数据判断和记录日志
            sql = "insert into subway.sw_sclcacc_exception_log (TRADE_DATE,EXCEPTION_TYPE,EXCEPTION_FILE_NAME,LINEID,STATIONID,CREATE_TIME,UPDATE_TIME,ERR_TYPE,ERR_MSG) " \
                  " select '%s','3','acc_fare_statistics'," \
                  " b.line_code,b.code,now(),now(),'2','车站ACC无数据'" \
                  " from subway.sw_param_station b left join subway.acc_fare_statistics a " \
                  " on b.line_code = a.lineid and b.code = a.station_number and a.bookdate = '%s'" \
                  " WHERE a.station_number is null and b.IS_ACTIVE = 1;" % (self.bookdate, self.bookdate)
            sqlList.append(sql)
            # ACC清分结果表判断和记录日志
            sql = "insert into subway.sw_sclcacc_exception_log (TRADE_DATE,EXCEPTION_TYPE,EXCEPTION_FILE_NAME,LINEID,STATIONID,CREATE_TIME,UPDATE_TIME,ERR_TYPE,ERR_MSG) " \
                  " select '%s','3','TCONSCLEAN'," \
                  " b.code,'',now(),now(),'2','ACC无按类型清分数据'" \
                  " from subway.sw_param_line b left join subway.SW_CK_SYS_CLEANING_CONS_TYPE a " \
                  " on b.code = a.LINE_CODE and a.TRADE_DATE = '%s'" \
                  " WHERE a.LINE_CODE is null and b.IS_ACTIVE = 1;" % (self.bookdate, self.bookdate)
            sqlList.append(sql)
            sql = "insert into subway.sw_sclcacc_exception_log (TRADE_DATE,EXCEPTION_TYPE,EXCEPTION_FILE_NAME,LINEID,STATIONID,CREATE_TIME,UPDATE_TIME,ERR_TYPE,ERR_MSG) " \
                  " select '%s','3','PCONSCLEAN'," \
                  " b.code,'',now(),now(),'2','ACC无按票种清分数据'" \
                  " from subway.sw_param_line b left join subway.SW_CK_SYS_CLEANING_CONS_TICKET a " \
                  " on b.code = a.LINE_CODE and a.TRADE_DATE = '%s'" \
                  " WHERE a.LINE_CODE is null and b.IS_ACTIVE = 1;" % (self.bookdate, self.bookdate)
            sqlList.append(sql)

        # 插入通知
        sql = "insert into sw_message(TITLE,MSG_TYPE,CONTENT,STATUS,URL,CREATE_TIME,DEPT_ID) " \
              " select concat(a.TRADE_DATE,'日',case a.EXCEPTION_TYPE when '1' then concat(c.name,b.name,'SC接口异常') when '2' then concat(c.name,b.name,'LC接口异常') when '3' then 'ACC接口异常' end),'通知',case a.EXCEPTION_TYPE when '1' then concat(c.name,b.name,a.ERR_TYPE) when '2' then concat(c.name,b.name,a.ERR_TYPE) when '3' then concat('ACC接口异常:',a.ERR_TYPE) end,0,'',now(),27 " \
              " from subway.sw_sclcacc_exception_log a " \
              " left join subway.sw_param_station b on b.line_code = a.LINEID and b.code = a.STATIONID " \
              " left join subway.sw_param_line c on c.code = a.LINEID " \
              " WHERE a.TRADE_DATE = '%s' and b.IS_ACTIVE = 1 and c.IS_ACTIVE = 1 ;" % (self.bookdate)
        sqlList.append(sql)

        mysql.executeAll(sqlList)

    # 对账文件最终数据处理
    def reconciliation_finnal(self,item_tuple):
        global mysql
        gol.set_value('dbName', "transaction_dataacc")
        inSqlList = []
        date_obj = datetime.datetime.strptime(self.bookdate,'%Y%m%d')
        weekday_index = str(date_obj.weekday())
        if 'F106' in item_tuple:
            # 清分对账文件统计并汇总到清分按业务统计表sw_ck_sys_cleaning_cons_type
            sql = "INSERT INTO subway.sw_ck_sys_cleaning_cons_type (SERVICE_ORG,CLEAN_TYPE,TRADE_DATE,LINE_CODE,productIssuerId,MONEY,CLEAN_NUMBER) " \
                  " SELECT line_OperationID,CleanType,REPLACE(OperStat_date,'\t',''),line_number,ticket_issuer,SUM(REPLACE(CleanAmt,'\t','')),SUM(REPLACE(CleanNum,'\t','')) " \
                  "FROM ReconciliationCleanData " \
                  "GROUP BY line_OperationID,CleanType,OperStat_date,line_number,ticket_issuer ;"
            inSqlList.append(sql)

        array = const.DZTABLELIST
        for inDict in array:
            if inDict['filetype'] in item_tuple:
                # 第一个元组为对应的主表
                tblname = inDict["tablelist"][0]
                sql = "DROP TABLE IF EXISTS {0}_{1};".format(tblname,weekday_index)
                #print(sql)
                inSqlList.append(sql)
                sql = "CREATE TABLE {0}_{1} AS SELECT * FROM {0};".format(tblname,weekday_index)
                #print(sql)
                inSqlList.append(sql)

        mysql.executeAll(inSqlList)

        # 计算周几
        """
        sql = "DROP TABLE IF EXISTS ReconciliationScopeData_ypt_{0};".format(weekday_index)
        mysql.executeAll([sql])
        sql = " DROP TABLE IF EXISTS ReconciliationScopeData_ykt_{0}; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "DROP TABLE IF EXISTS ReconciliationScopeData_jtk_{0}; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "DROP TABLE IF EXISTS ReconciliationScopeData_ydjtk_{0}; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "DROP TABLE IF EXISTS ReconciliationErrorDetail_ypt_{0}; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "DROP TABLE IF EXISTS ReconciliationErrorDetail_ykt_{0}; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "DROP TABLE IF EXISTS ReconciliationErrorDetail_jtk_{0}; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "DROP TABLE IF EXISTS ReconciliationErrorDetail_ydjtk_{0}; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "DROP TABLE IF EXISTS ReconciliationAdjustDetail_ypt_{0}; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "DROP TABLE IF EXISTS ReconciliationAdjustDetail_ykt_{0}; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "DROP TABLE IF EXISTS ReconciliationAdjustDetail_jtk_{0}; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "DROP TABLE IF EXISTS ReconciliationAdjustDetail_ydjtk_{0}; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "DROP TABLE IF EXISTS ReconciliationStatData_ypt_{0}; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "DROP TABLE IF EXISTS ReconciliationStatData_ykt_{0}; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "DROP TABLE IF EXISTS ReconciliationStatData_jtk_{0}; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "DROP TABLE IF EXISTS ReconciliationStatData_ydjtk_{0}; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "DROP TABLE IF EXISTS ReconciliationQrTicketError_{0}; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "DROP TABLE IF EXISTS ReconciliationCleanData_{0}; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "DROP TABLE IF EXISTS ReconciliationCashIncome_{0}; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "CREATE TABLE ReconciliationScopeData_ypt_{0} AS SELECT * FROM ReconciliationScopeData_ypt;".format(weekday_index)
        mysql.executeAll([sql])
        sql = " CREATE TABLE ReconciliationScopeData_ykt_{0} AS SELECT * FROM ReconciliationScopeData_ykt; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "CREATE TABLE ReconciliationScopeData_jtk_{0} AS SELECT * FROM ReconciliationScopeData_jtk; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "CREATE TABLE ReconciliationScopeData_ydjtk_{0} AS SELECT * FROM ReconciliationScopeData_ydjtk; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "CREATE TABLE ReconciliationErrorDetail_ypt_{0} AS SELECT * FROM ReconciliationErrorDetail_ypt; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "CREATE TABLE ReconciliationErrorDetail_ykt_{0} AS SELECT * FROM ReconciliationErrorDetail_ykt; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "CREATE TABLE ReconciliationErrorDetail_jtk_{0} AS SELECT * FROM ReconciliationErrorDetail_jtk; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "CREATE TABLE ReconciliationErrorDetail_ydjtk_{0} AS SELECT * FROM ReconciliationErrorDetail_ydjtk; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "CREATE TABLE ReconciliationAdjustDetail_ypt_{0} AS SELECT * FROM ReconciliationAdjustDetail_ypt; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "CREATE TABLE ReconciliationAdjustDetail_ykt_{0} AS SELECT * FROM ReconciliationAdjustDetail_ykt; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "CREATE TABLE ReconciliationAdjustDetail_jtk_{0} AS SELECT * FROM ReconciliationAdjustDetail_jtk; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "CREATE TABLE ReconciliationAdjustDetail_ydjtk_{0} AS SELECT * FROM ReconciliationAdjustDetail_ydjtk; ".format(weekday_index)
        mysql.executeAll([sql])

        sql = "CREATE TABLE ReconciliationStatData_ypt_{0} AS SELECT * FROM ReconciliationStatData_ypt; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "CREATE TABLE ReconciliationStatData_ykt_{0} AS SELECT * FROM ReconciliationStatData_ykt; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "CREATE TABLE ReconciliationStatData_jtk_{0} AS SELECT * FROM ReconciliationStatData_jtk; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "CREATE TABLE ReconciliationStatData_ydjtk_{0} AS SELECT * FROM ReconciliationStatData_ydjtk; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "CREATE TABLE ReconciliationQrTicketError_{0} AS SELECT * FROM ReconciliationQrTicketError; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "CREATE TABLE ReconciliationCleanData_{0} AS SELECT * FROM ReconciliationCleanData; ".format(weekday_index)
        mysql.executeAll([sql])
        sql = "CREATE TABLE ReconciliationCashIncome_{0} AS SELECT * FROM ReconciliationCashIncome; ".format(weekday_index)
        mysql.executeAll([sql])
        """
    # 【9号线】通过BOM票房结算数据生成日票售卖
    def bom_settledata_deal(self):
        global mysql
        sqlList = []
        # 签到
        sql = "INSERT INTO subway.sc_conductor_settle_classes (LINE_CODE,STATION_CODE,WORKING_START_TIME,WORKING_END_TIME,SETTLE_TIME, " \
              "CONDUCTOR_NAME,CONDUCTOR_JOB_NO,BOM_CODE,DEVICE_OPERATE_NUM,CREATE_TIME) " \
              " SELECT DISTINCT CONV(SUBSTR(REPLACE(a.equipment_number,'\t',''),3,2),16,10) LINE_CODE,CONV(SUBSTR(REPLACE(a.equipment_number,'\t',''),5,2),16,10) STATION_CODE,STR_TO_DATE(a.settle_time_begin,'%Y%m%d%H%i%s') WORKING_START_TIME,STR_TO_DATE(a.settle_time_end,'%Y%m%d%H%i%s') WORKING_END_TIME,STR_TO_DATE(a.stat_date,'%Y%m%d') SETTLE_TIME, " \
              " (select b.nikename from sys_user b where b.DEL_FLAG='0' and b.device_operate_no=REPLACE(a.settle_operator,'\t','') limit 1) nikename,(select b.user_code from sys_user b where b.DEL_FLAG='0' and b.device_operate_no=REPLACE(a.settle_operator,'\t','') limit 1) user_code,CONCAT('BOM',LPAD(CONV(SUBSTR(REPLACE(a.equipment_number,'\t',''),8,2),16,10),3,'0')) BOM_CODE,REPLACE(a.settle_operator,'\t','') device_operate_no,now() CREATE_TIME " \
              " FROM transaction_datasc.BOMSettleData a " \
              " WHERE 1=1; "
        sqlList.append(sql)
        # 日票售卖
        sql = "INSERT INTO subway.sc_conductor_market_sale (LINE_CODE,STATION_CODE,SETTLE_TIME, " \
              " CONDUCTOR_NAME,CONDUCTOR_JOB_NO,BOM_CODE,DEVICE_OPERATE_NUM, " \
              " TICKET_TYPE,TICKET_SUB_TYPE,PAYMETHOD,SALE_NUM,SALE_AMOUNT,CREATE_TIME) " \
              " SELECT CONV(SUBSTR(REPLACE(a.equipment_number,'\t',''),3,2),16,10) LINE_CODE,CONV(SUBSTR(REPLACE(a.equipment_number,'\t',''),5,2),16,10) STATION_CODE,STR_TO_DATE(a.stat_date,'%Y%m%d') SETTLE_TIME, " \
              "  (select b.nikename from sys_user b where b.DEL_FLAG='0' and b.device_operate_no=REPLACE(a.settle_operator,'\t','') limit 1) nikename,(select b.user_code from sys_user b where b.DEL_FLAG='0' and b.device_operate_no=REPLACE(a.settle_operator,'\t','') limit 1) user_code,CONCAT('BOM',LPAD(CONV(SUBSTR(REPLACE(a.equipment_number,'\t',''),8,2),16,10),3,'0')) BOM_CODE,REPLACE(a.settle_operator,'\t','') device_operate_no, " \
              " a.productType,a.productSubType,a.PaymentType,a.TxnNum,a.TxnAmt,now() " \
              " FROM transaction_datasc.BOMSettleData a " \
              " WHERE a.productType=6 ; "
        sqlList.append(sql)
        # 全部记录备用
        sql = "INSERT INTO subway.sc_bom_settledata (LINE_CODE,STATION_CODE,CONDUCTOR_NAME,CONDUCTOR_JOB_NO,BOM_CODE, " \
              " data_source,equipment_number,settle_seq,settle_date,settle_time_begin,settle_time_end,settle_operator,stat_date, " \
              " productType,productSubType,TxnType,TxnSubType,TxnNum,DebitAmt,TxnAmt,CommissionAmt,CostAmt,PaymentType, " \
              " reserve1,reserve2,reserve3,reserve4,reserve5,reserve6,CREATE_TIME) " \
              " SELECT CONV(SUBSTR(REPLACE(a.equipment_number,'\t',''),3,2),16,10) LINE_CODE,CONV(SUBSTR(REPLACE(a.equipment_number,'\t',''),5,2),16,10) STATION_CODE,(select b.nikename from sys_user b where b.DEL_FLAG='0' and b.device_operate_no=REPLACE(a.settle_operator,'\t','') limit 1) nikename,(select b.user_code from sys_user b where b.DEL_FLAG='0' and b.device_operate_no=REPLACE(a.settle_operator,'\t','') limit 1) user_code,CONCAT('BOM',LPAD(CONV(SUBSTR(REPLACE(a.equipment_number,'\t',''),8,2),16,10),3,'0')) BOM_CODE, " \
              " a.data_source,a.equipment_number,a.settle_seq,a.settle_date,a.settle_time_begin,a.settle_time_end,REPLACE(a.settle_operator,'\t','') device_operate_no,a.stat_date, " \
              " a.productType,a.productSubType,a.TxnType,a.TxnSubType,a.TxnNum,a.DebitAmt,a.TxnAmt,a.CommissionAmt,a.CostAmt,a.PaymentType, " \
              " reserve1,reserve2,reserve3,reserve4,reserve5,reserve6,now() " \
              " FROM transaction_datasc.BOMSettleData a " \
              " WHERE 1=1 ; "
        sqlList.append(sql)

        mysql.executeAll(sqlList)

    def mainMethod(self):

        dealFileScope = []
        # 调用参数代表处理的接口类型范围
        dealFileScope = sys.argv[1].split(',')


        # 清空昨日50+数据库数据
        self.removeLastDayFromSql(dealFileScope)
        self.log.info("清空昨日50+数据库数据成功")

        # 解析
        self.anlysisData(dealFileScope)
        self.log.info("解析数据成功")

        # acc 数据入库 入 subway 库
        #sourceRoot = self.cf.get("option", "source_file")
        #self.accFaretoSql(sourceRoot + "acc/")
        #self.log.info("acc 数据入库成功")

        # 归类统计 入tmp
        if 'sc' in dealFileScope:
            gol.set_value('dbName', "transaction_datasc")
            self.statisticsToSql()
            self.log.info("SC一次归类统计成功")
            # 2次归类统计 入subway fare库
            self.finalStatistics("sc_fare_statistics")
            self.log.info("SC二次归类统计成功")
            # # 出站入库
            self.sc_out_statistics("sc")
            self.log.info("SC出站入库统计成功")
            # 钱箱入账
            self.box5109ToSubway()
            self.log.info("钱箱入账完成")
            # 数据比较差异入库
            # sw_au_tvm_amount_defference
            self.updateFinalForType231("sc", "2")
            self.log.info("SC层级TVM差异计算完成")
            # sw_au_tvm_box_amount_difference
            self.updateFinalForType232()
            self.log.info("SC层级TVM钱箱差异计算完成")
            # SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE
            self.updateFinalForType2314("sc", "1")
            self.log.info("SC层级BOM差异计算完成")
            # # 出站票统计
            self.out_ticket_all("sc")
            self.log.info("SC出站票统计成功")
            # 补钱箱
            self.bu_qian_xiang()
            self.log.info("补钱箱完成")
            # 【9号线】通过BOM票房结算数据生成日票售卖
            self.bom_settledata_deal()
            self.log.info("通过BOM票房结算数据生成日票售卖和签到数据完成")
            # 异常日志
            self.exception_logging("sc")
            self.log.info("SC接口数据异常信息生成完成")
        if 'lc' in dealFileScope:
            gol.set_value('dbName', "transaction_datalc")
            self.statisticsToSql()
            self.log.info("LC一次归类统计成功")
            # 2次归类统计 入subway fare库
            self.finalStatistics("lc_fare_statistics")
            self.log.info("LC二次归类统计成功")
            # # 出站入库
            self.sc_out_statistics("lc")
            self.log.info("LC出站入库统计成功")
            # 数据比较差异入库
            # sw_au_tvm_amount_defference
            self.updateFinalForType231("lc", "3")
            self.log.info("LC层级TVM差异计算完成")
            # SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE
            self.updateFinalForType2314("lc", "2")
            self.log.info("LC层级BOM差异计算完成")
            # # 出站票统计
            self.out_ticket_all("lc")
            self.log.info("LC出站票统计成功")
            # 异常日志
            self.exception_logging("lc")
            self.log.info("LC接口数据异常信息生成完成")
        if 'aclc' in dealFileScope:
            #
            # self.updataToFinalACC()
            # self.log.info("ACC数据比较差异成功")
            # # #
            #
            # self.out_ticket_acc()
            # 日报表生成
            # self.dayDataSave()
            pass
        if ('sc' in dealFileScope) or ('lc' in dealFileScope):
            # # # 层级数据比较差异入库
            # self.updataToFinal()
            # sw_au_conductor_hierarchy_amount_difference
            self.updateFinalForType2315("sc_fare_statistics", "lc_fare_statistics", "1")
            # SW_AU_ITVM_ONLINE_AMOUNT_HIERARCHY_DIFFERENCE
            self.updateFinalForType239("sc_fare_statistics", "lc_fare_statistics", "2", "3")
            self.log.info("SC、LC层级数据比较差异计算完成")

        # self.update_day_cell()
        # self.log.info("库存日报表日报表统计数据成功")
        # #
        # 清空源文件夹文件
        #sourceRoot = self.cf.get("option", "source_file")
        #del_file(sourceRoot)
        #self.log.info("清空源文件夹文件成功")


if __name__ == "__main__":
    dt = DownLoadTools()
    dt.mainMethod()
